Need helping writing a Query, if it's possible....

[Guest]

Hello,

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 "CustomerNumber"

Sample data:

CUSTOMERS
CustomerNumber
-----------------
1
2
3

PARTIES
CustomerNumber Party
------------------------------------
1 Birthday Party
1 Thanksgiving Party
1 Christmas Party
2 Birthday Party
2 Halloween Party


What I need is a Query to produce the following, in this format:

CustomerNumber Party1 Party2
Party3
------------------------------------------------------------------------------
1 Birthday Party Thanksgiving Party
Christmas Party
2 Birthday Party Halloween Party
3


Can this be done with Queries ? If so what is the code ?

Any help would be greatly appreciated.

Thank you,
Jeff

 

[Duane Hookom]

You should be able to combine the two tables in a crosstab query.
CustomerNumber would be the Row Heading
First of Party would be the Value
The column heading would be:
"Party" & DCount("*","PARTIES", "CustomerNumber = " &
CUSTOMERS![CustomerNumber] & " AND PARTY <=""" & [Party] & """")

 

[Guest]

Hi Duane,

Thank you for taking the time to respond but what you wrote doesnt help me.

First I dont even understand where to put what code you wrote, second is it
possible to give me the whole SQL query ? I am not familiar with using the
Corss Tab wizard, can you create it & then if it works give me the SQL ?

Or anyone else can help me ?

Thank you,
Jeff

You should be able to combine the two tables in a crosstab query.
CustomerNumber would be the Row Heading
First of Party would be the Value
The column heading would be:
"Party" & DCount("*","PARTIES", "CustomerNumber = " &
CUSTOMERS![CustomerNumber] & " AND PARTY <=""" & [Party] & """")

--
Duane Hookom
MS Access MVP

Hello,

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 "CustomerNumber"

Sample data:

CUSTOMERS
CustomerNumber
-----------------
1
2
3

PARTIES
CustomerNumber Party
------------------------------------
1 Birthday Party
1 Thanksgiving Party
1 Christmas Party
2 Birthday Party
2 Halloween Party


What I need is a Query to produce the following, in this format:

CustomerNumber Party1 Party2
Party3
------------------------------------------------------------------------------
1 Birthday Party Thanksgiving Party
Christmas Party
2 Birthday Party Halloween Party
3


Can this be done with Queries ? If so what is the code ?

Any help would be greatly appreciated.

Thank you,
Jeff

 

[Duane Hookom]

I expect you know how to create a standard select query based on your
tables. Use the menu to change your query to a crosstab query.

Then re-read my previous post to see which columns/fields are the Row
Heading, Value, and Column Headings.

--
Duane Hookom
MS Access MVP

Hi Duane,

Thank you for taking the time to respond but what you wrote doesnt help
me.

First I dont even understand where to put what code you wrote, second is
it
possible to give me the whole SQL query ? I am not familiar with using
the
Corss Tab wizard, can you create it & then if it works give me the SQL ?

Or anyone else can help me ?

Thank you,
Jeff

You should be able to combine the two tables in a crosstab query.
CustomerNumber would be the Row Heading
First of Party would be the Value
The column heading would be:
"Party" & DCount("*","PARTIES", "CustomerNumber = " &
CUSTOMERS![CustomerNumber] & " AND PARTY <=""" & [Party] & """")

--
Duane Hookom
MS Access MVP

Hello,

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 "CustomerNumber"

Sample data:

CUSTOMERS
CustomerNumber
-----------------
1
2
3

PARTIES
CustomerNumber Party
------------------------------------
1 Birthday Party
1 Thanksgiving Party
1 Christmas Party
2 Birthday Party
2 Halloween Party


What I need is a Query to produce the following, in this format:

CustomerNumber Party1 Party2
Party3
------------------------------------------------------------------------------
1 Birthday Party Thanksgiving Party
Christmas Party
2 Birthday Party Halloween Party
3


Can this be done with Queries ? If so what is the code ?

Any help would be greatly appreciated.

Thank you,
Jeff