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 said:
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...
