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

J

Jeff

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
 
D

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] & """")
 
J

Jeff

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

Duane Hookom said:
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


Jeff said:
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
Click to expand...
Click to expand...
 
D

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


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

Duane Hookom said:
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


Jeff said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

RE post - Need help writing a Query 1
access query trouble-HELP! 1
Automatically sending multiple parts of text from one word doc to another - is possible. 0
Display message if specific query returns a value 4
How to display primary fields only once. 2
linked fields in a query 3
Is this possible? 0
Need Help With Determining Age 2

Top