pi********@hotmail.com (Pieter Linden) wrote in
news:bf**************************@posting.google.c om:
da**********@yahoo.com (David Mills) wrote in message
news:<8b**************************@posting.google. com>... Hello, looking for some input on how to do this query. (I'm using
Access)
John Doe 123 Main St Chicago 123
John Doe 123 Main St Chicago 456
John Doe 123 Main St Chicago 789
Given the above data (thousands of customers by the way) how can I
get a query to return the following?
Column1 Column2 Column3 Column4
John Doe 123 456 789
I don't even know if this is possible, I can't figure it out.
Thanks for any help.
don't think you can denormalize your data with just a query... I would
probably output my data to Excel and see if I couldn't do a
Transpose...
You could write a static function like this:
static function InstanceOf(something) as integer
static somethingelse
if somethingelse = something then
InstanceOf = InstanceOf+1
else
InstanceOf = 1
end if
end function
Then, you can add this function to your query (important: it needs to be
ordered!)
select InstanceOf(Name) as Instance, name, address, city, code
from theTable
order by name
to get this:
1 John Doe 123 Main St Chicago 123
2 John Doe 123 Main St Chicago 456
3 John Doe 123 Main St Chicago 789
....now, you can do a cross-tab based on this query. Set name, address,
city as row headers, the Instance field as the column header, and first
(code) as the value. You need to apply a summary function to the field
intended to be the Value field in the crosstab.
This will get you:
1 2 3
John Doe 123 Main St Chicago 123 456 789
etc.
The drawback about this is that the function will fire off at just about
every excuse that the query has for being refreshed... But since Access
doesn't have anything like a Rownum pseudocolumn field like Oracle, or an
equivalant in Sql Server...
Having an autonumber field won't work, either. You cannot guarantee
sequential inserts of similar data, right?
