I've always been stumped by this:

I have 6 tables joined in SQL and I want to end up with an Excel shee
that has one row per user. Tables are mostly one to one but two aren't
Example is userdata.lastname userdata.firstame, userequip.equiplist (on
to many), sessions.sessiontitle, sessionformats.format, etc.

I want to have a row that has the sessiontitle in column A, th
presenter last name in B, first name in C (if there is more than on
presenter...not sure what to do) sessionformat in D etc. equipmen
would go across the columns (pencil, paper, blackboard)

If someone could explain the technique for this...I would be foreve
grateful

--
ovrdrv
-----------------------------------------------------------------------
ovrdrvn's Profile: http://www.excelforum.com/member.php...fo&userid=3357
View this thread: http://www.excelforum.com/showthread.php?threadid=53351

What database? This matters for a SQL-based approach.

You do not mention what method you're using to fetch information to Excel ?
Are you using the built-in query tools, or a "hand-coded" routine (ADO/DAO) ?

Is the list of possible "equipment" values known up front, or could it vary ?

Without any of the above information I could only suggest using ADO to fetch the resultset (including the "duplicate" rows) and then
looping through the records. When you hit a new user write out the user-specific info: if not a new user then just write the
"equipment" field (incrementing the column for each record).

You might also look at shaped recordsets in ADO but this might be more complex than the approach above...

Tim

--
Tim Williams
Palo Alto, CA


"ovrdrvn" wrote in message
...

I've always been stumped by this:

I have 6 tables joined in SQL and I want to end up with an Excel sheet
that has one row per user. Tables are mostly one to one but two aren't.
Example is userdata.lastname userdata.firstame, userequip.equiplist (one
to many), sessions.sessiontitle, sessionformats.format, etc.

I want to have a row that has the sessiontitle in column A, the
presenter last name in B, first name in C (if there is more than one
presenter...not sure what to do) sessionformat in D etc. equipment
would go across the columns (pencil, paper, blackboard)

If someone could explain the technique for this...I would be forever
grateful.


--
ovrdrvn
------------------------------------------------------------------------
ovrdrvn's Profile: http://www.excelforum.com/member.php...o&userid=33573
View this thread: http://www.excelforum.com/showthread...hreadid=533518

You also didn't mention rather if you are using a third party tool such as
"Showcase Query" or not. We use ShowCase Query to bring data into Excel
from our main DB program, though some still use Access for those items
that's not in the main DB program.

If at all possible, I would stay away from MS Query due to it's ADO memory
leak issues. I have attempted to use the MS Query only for it to be more of
a pain than it's worth and not only that, but it's leak is quite severe. I
have done plenty of SQL writing within Showcase Query (It's visual method as
well as the written form), as well as Access (both QueryByExample and the
SQL code within the VBA environment), which I have also spent a rather
significant amount of time in both VBAs, Excel and Access. I don't
particularly care for Access as it has some major critical issues for
multiple user environment and it's error checking is so UNuserfriendly for
mouse users, I had to recreate my own centralized error checking coding,
which then in order to use it, I had to have every single form and control
unbound, which then I ran into issues with the EditMode property not working
appropriately via DAO coding, and ADO coding doesn't allow for use of the
Dynamic CursorKeyset against the Jet Engine.

Note, This is using Office 2002 and as far as Office 2003 is concerned, that
doesn't appear to be much better than Office 2002, if any, as far as I'm
concerned. Office 97 to Office 2000 was a quantum leap as I hated Office 97
with the passion to the point that I would rather work with Lotus 1-2-3,
v2.3 than I would with Office 97 as At least that version of Lotus (Though
it's DOS based) is very stable as compared to Office 97, which it's so
unstable (Even with it's latest patches installed), I ended up having to
move to Office 2000 just for the stability. Since Office 2000 though, there
has been no significant change.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
What database? This matters for a SQL-based approach.

You do not mention what method you're using to fetch information to Excel
?
Are you using the built-in query tools, or a "hand-coded" routine
(ADO/DAO) ?

Is the list of possible "equipment" values known up front, or could it
vary ?

Without any of the above information I could only suggest using ADO to
fetch the resultset (including the "duplicate" rows) and then
looping through the records. When you hit a new user write out the
user-specific info: if not a new user then just write the
"equipment" field (incrementing the column for each record).

You might also look at shaped recordsets in ADO but this might be more
complex than the approach above...

Tim

--
Tim Williams
Palo Alto, CA


"ovrdrvn" wrote in
message
...

I've always been stumped by this:

I have 6 tables joined in SQL and I want to end up with an Excel sheet
that has one row per user. Tables are mostly one to one but two aren't.
Example is userdata.lastname userdata.firstame, userequip.equiplist (one
to many), sessions.sessiontitle, sessionformats.format, etc.

I want to have a row that has the sessiontitle in column A, the
presenter last name in B, first name in C (if there is more than one
presenter...not sure what to do) sessionformat in D etc. equipment
would go across the columns (pencil, paper, blackboard)

If someone could explain the technique for this...I would be forever
grateful.


--
ovrdrvn
------------------------------------------------------------------------
ovrdrvn's Profile:
http://www.excelforum.com/member.php...o&userid=33573
View this thread:
http://www.excelforum.com/showthread...hreadid=533518

OK, we have data in MS SQl Server 2000. Tried a DTS package with a SQL
query that works in Crystal Reports or a tool where I can manually
manipulate layout. Problem comes when I need Excel for a mail merge and
need one row per user.

Was wondering if going from Excel to SQL Server with some programming
might work?


--
ovrdrvn
------------------------------------------------------------------------
ovrdrvn's Profile: http://www.excelforum.com/member.php...o&userid=33573
View this thread: http://www.excelforum.com/showthread...hreadid=533518

Are you familiar enough with ADO to hand-code the query as a macro ?

Tim

--
Tim Williams
Palo Alto, CA


"ovrdrvn" wrote in message
...

OK, we have data in MS SQl Server 2000. Tried a DTS package with a SQL
query that works in Crystal Reports or a tool where I can manually
manipulate layout. Problem comes when I need Excel for a mail merge and
need one row per user.

Was wondering if going from Excel to SQL Server with some programming
might work?


--
ovrdrvn
------------------------------------------------------------------------
ovrdrvn's Profile: http://www.excelforum.com/member.php...o&userid=33573
View this thread: http://www.excelforum.com/showthread...hreadid=533518

If you can either point me in the right diretion I will either make an
attempt or go fishing for a buddy who might be able to.

I work for a educational based charity, so we have limited resources.
We get fairly nice donations from Microsoft etc and "some times" I can
attempt to get help through them (not often mind you)

Thanks for any assistance you can provide.


--
ovrdrvn
------------------------------------------------------------------------
ovrdrvn's Profile: http://www.excelforum.com/member.php...o&userid=33573
View this thread: http://www.excelforum.com/showthread...hreadid=533518

Lots of examples using ADO on the web, particularly with SQL Server.

Once you have a recordset (ordered by the "Name" fields) you can loop
through the records: if the name is new then start a new row in your
Excel sheet: if not then increment your column counter and just write
out the "equipment" field.

You can contact me directly if you're not able to find anyone "local"
to help.

tim j williams at gmail dot com

Tim