Jeff said:
Hello,
I had just posted this but I think it was beig misunderstood with the
previous data so I am re-posting with better data.
I will do my best to explain what I need. I am not sure if it is even
possible to do with a Query, but here it goes. I would need the whole SQL
query if it is possible.
I have a Table CUSTOMERS.
Then I have another Table PARTIES where there are multiple Parties for each
Customer.
They are linked by: CustomerCode
Sample data:
CUSTOMERS
CustomerCode
It's not obvious here what the sorting order is, so I sorted by the
[CustomerCode] field (new version, revised from [CustomerNumber] in a
previous message, but I didn't rename it here). This puts the empty
record first in my example. So my sorted version looks like this, and
[CustomerNumber] is its (unique) primary key:
[Customers]
CustomerNumber
--------------
AB500
AX993
GH200
PARTIES
CustomerCode Party
------------------------------------
AX993 Birthday Party
AX993 Thanksgiving Party
AX993 Christmas Party
GH200 Birthday Party
GH200 Halloween Party
In this one, there was NO obvious sorting order. (It was apparently not
alphabetical by party name!) So, I added a [Date] field; perhaps a more
descriptive name would be better, such as [DatePartyIsScheduled], but it
takes extra time to type long names.
[Parties]
CustomerNumber Party Date
-------------- ------------------ ----
AX993 Thanksgiving Party 11/20/2005
GH200 Halloween Party 10/29/2005
GH200 Birthday Party 11/10/2005
AX993 New Year's Party 12/31/2005
AX993 Christmas Party 12/10/2005
AX993 Birthday Party 1/1/2006
Incidentally, if the same party name keeps popping up, such as "Birthday
Party", you might consider putting those names into another Table and
just linking to that Table.
What I need is a Query to produce the following, in this format:
CustomerCode Party1 Party2 Party3
I did that (see below), except that the blank record is first in my
sorting order. If you want to specify the order you list here, you'll
need to include some field which will produce that.
Can this be done with Queries ? If so what is the SQL code, Please can just
give me the SQL Code ?
Any help would be greatly appreciated.
Thank you,
Jeff
First thing I needed to do was to determine into which column a
particular party should be placed. This Query does that, using as its
sorting key the [Date] field that I added:
[Q_PartiesSequenced]
SELECT Parties.CustomerNumber, Parties.Party,
Count(Parties_1.Date) AS Sequence
FROM Parties INNER JOIN Parties AS Parties_1
ON Parties.CustomerNumber = Parties_1.CustomerNumber
WHERE (((Parties_1.Date)<=[Parties].[Date]))
GROUP BY Parties.CustomerNumber, Parties.Party
ORDER BY Parties.CustomerNumber, Count(Parties_1.Date);
CustomerNumber Party Sequence
-------------- ----- --------
AX993 Thanksgiving Party 1
AX993 Christmas Party 2
AX993 New Year's Party 3
AX993 Birthday Party 4
GH200 Halloween Party 1
GH200 Birthday Party 2
This could easily be filtered to delete records whose dates have passed
or are too far in the future.
Next Query grabs all parties that belong in the "Party1" column:
[Q_PartiesSeq1]
SELECT Parties.CustomerNumber, Parties.Party AS Party1
FROM Parties INNER JOIN Parties AS Parties_1
ON Parties.CustomerNumber = Parties_1.CustomerNumber
WHERE (((Parties_1.Date)<=[Parties].[Date]))
GROUP BY Parties.CustomerNumber, Parties.Party
HAVING (((Count(Parties_1.Date))=1))
ORDER BY Parties.CustomerNumber, Count(Parties_1.Date);
CustomerNumber Party1
-------------- ------
AX993 Thanksgiving Party
GH200 Halloween Party
[Q_PartiesSeq2] and [Q_PartiesSeq3] are defined similarly, except with
"2" or "3" in some places where "1" appears in SQL for [Q_PartiesSeq1].
Having defined [Q_PartiesSeq1], [Q_PartiesSeq2], and [Q_PartiesSeq3], we
can now produce the basis for your report:
[Q_Parties_Next_3]
SELECT Customers.CustomerNumber, Q_PartiesSeq1.Party1,
Q_PartiesSeq2.Party2, Q_PartiesSeq3.Party3
FROM ((Customers
LEFT JOIN Q_PartiesSeq1
ON Customers.CustomerNumber = Q_PartiesSeq1.CustomerNumber)
LEFT JOIN Q_PartiesSeq2
ON Customers.CustomerNumber = Q_PartiesSeq2.CustomerNumber)
LEFT JOIN Q_PartiesSeq3
ON Customers.CustomerNumber = Q_PartiesSeq3.CustomerNumber
ORDER BY Customers.CustomerNumber;
CustomerNumber Party1 Party2 Party3
-------------- ------------------ --------------- -----------------
AB500
AX993 Thanksgiving Party Christmas Party New Year's Party
GH200 Halloween Party Birthday Party
.... which is close to what you requested.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
