1 table inserted multiple times in a query

G

Guest

Thanks to all that look and assist.

I have 1 Table with 3 fields:
Number (auto number), Customer, Order number

The customers in this table may have anywhere between 1 and 3 orders. What
I'm wanting to do is build a query that will put the order numbers in a
horizontal line. Let's say John Doe has 3 orders and Jane Doe has 2 orders.

Table output example:
Number | Customer | Order Number
1 |John Doe |1
2 |Jane Doe |1
3 |Jane Doe |2
4 |John Doe |2
5 |John Doe |3

I'm wanting the Query to pull the data together to give me 1 line per customer

Query output Example:
Line1: John Doe | Order Number 1 | Order number 2 | Order number 3
Line2: Jane Doe | Order Number 1 | Order number 2 | BLANK

How do I build the query?
 
V

Vincent Johns

Fred said:
Thanks to all that look and assist.

I have 1 Table with 3 fields:
Number (auto number), Customer, Order number

The customers in this table may have anywhere between 1 and 3 orders. What
I'm wanting to do is build a query that will put the order numbers in a
horizontal line. Let's say John Doe has 3 orders and Jane Doe has 2 orders.

Table output example:
Number | Customer | Order Number
1 |John Doe |1
2 |Jane Doe |1
3 |Jane Doe |2
4 |John Doe |2
5 |John Doe |3

I'm wanting the Query to pull the data together to give me 1 line per customer

Query output Example:
Line1: John Doe | Order Number 1 | Order number 2 | Order number 3
Line2: Jane Doe | Order Number 1 | Order number 2 | BLANK

How do I build the query?
Click to expand...

Here's one way, assuming "BLANK" is just a place-holder. :)

Define a Query to list just the names.

[Q_Names] SQL:
SELECT DISTINCT Orders.Customer
FROM Orders
ORDER BY Orders.Customer;

[Q_Names] Datasheet View:
Customer
--------
Jane Doe
John Doe

Then define a Query based on this to calculate a line number.

[Q_Count] SQL:
SELECT Q_Names.Customer, Count(Q_Names.Customer) AS Seq
FROM Q_Names, Q_Names AS Q_Names_1
WHERE (((Q_Names_1.Customer)>=[Q_Names].[Customer]))
GROUP BY Q_Names.Customer
ORDER BY Count(Q_Names.Customer);

[Q_Count] Datasheet View:
Customer Seq
--------- ---
John Doe 1
Jane Doe 2

Now you can format the contents of your Table to look good in your
datasheet, via a Query such as this one. I abbreviated the "Order
Number 1", etc., fields, but I assume you'll want to make major changes
anyway.

[Q_Orders] SQL:
SELECT "Line " & [Q_Count]![Seq] & ":" AS Line,
[Orders].[Customer], "Col_" & [Order Number] AS Header,
"Order # " & [Orders]![Order Number] AS Title
FROM Orders INNER JOIN Q_Count
ON [Orders].[Customer]=[Q_Count].[Customer]
ORDER BY [Orders].[Customer];

[Q_Orders] Datasheet View:
Line Customer Header Title
------- -------- ------ ---------
Line 2: Jane Doe Col_2 Order # 2
Line 2: Jane Doe Col_1 Order # 1
Line 1: John Doe Col_3 Order # 3
Line 1: John Doe Col_2 Order # 2
Line 1: John Doe Col_1 Order # 1

Then I defined a Crosstab Query to display the layout that you wanted.

[Q_Orders_Crosstab] SQL:
TRANSFORM First(Q_Orders.Title) AS FirstOfTitle
SELECT Q_Orders.Line, Q_Orders.Customer
FROM Q_Orders
GROUP BY Q_Orders.Line, Q_Orders.Customer
PIVOT Q_Orders.Header;

[Q_Orders_Crosstab] Datasheet View:
Line Customer Col_1 Col_2 Col_3
------- -------- --------- --------- ---------
Line 1: John Doe Order # 1 Order # 2 Order # 3
Line 2: Jane Doe Order # 1 Order # 2

There are other ways to do this, without using a Crosstab, but I think
this will do what you asked for. (Warning: If your data include an
order # 4 or others, you'll wind up with additional columns in the
Crosstab.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
G

Guest

Thanks Vincent. I see what you are doing and I will see how it works. The
"Blank" was just a place holder for an empty Cell.

Vincent Johns said:
Fred said:
Thanks to all that look and assist.

I have 1 Table with 3 fields:
Number (auto number), Customer, Order number

The customers in this table may have anywhere between 1 and 3 orders. What
I'm wanting to do is build a query that will put the order numbers in a
horizontal line. Let's say John Doe has 3 orders and Jane Doe has 2 orders.

Table output example:
Number | Customer | Order Number
1 |John Doe |1
2 |Jane Doe |1
3 |Jane Doe |2
4 |John Doe |2
5 |John Doe |3

I'm wanting the Query to pull the data together to give me 1 line per customer

Query output Example:
Line1: John Doe | Order Number 1 | Order number 2 | Order number 3
Line2: Jane Doe | Order Number 1 | Order number 2 | BLANK

How do I build the query?
Click to expand...

Here's one way, assuming "BLANK" is just a place-holder. :)

Define a Query to list just the names.

[Q_Names] SQL:
SELECT DISTINCT Orders.Customer
FROM Orders
ORDER BY Orders.Customer;

[Q_Names] Datasheet View:
Customer
--------
Jane Doe
John Doe

Then define a Query based on this to calculate a line number.

[Q_Count] SQL:
SELECT Q_Names.Customer, Count(Q_Names.Customer) AS Seq
FROM Q_Names, Q_Names AS Q_Names_1
WHERE (((Q_Names_1.Customer)>=[Q_Names].[Customer]))
GROUP BY Q_Names.Customer
ORDER BY Count(Q_Names.Customer);

[Q_Count] Datasheet View:
Customer Seq
--------- ---
John Doe 1
Jane Doe 2

Now you can format the contents of your Table to look good in your
datasheet, via a Query such as this one. I abbreviated the "Order
Number 1", etc., fields, but I assume you'll want to make major changes
anyway.

[Q_Orders] SQL:
SELECT "Line " & [Q_Count]![Seq] & ":" AS Line,
[Orders].[Customer], "Col_" & [Order Number] AS Header,
"Order # " & [Orders]![Order Number] AS Title
FROM Orders INNER JOIN Q_Count
ON [Orders].[Customer]=[Q_Count].[Customer]
ORDER BY [Orders].[Customer];

[Q_Orders] Datasheet View:
Line Customer Header Title
------- -------- ------ ---------
Line 2: Jane Doe Col_2 Order # 2
Line 2: Jane Doe Col_1 Order # 1
Line 1: John Doe Col_3 Order # 3
Line 1: John Doe Col_2 Order # 2
Line 1: John Doe Col_1 Order # 1

Then I defined a Crosstab Query to display the layout that you wanted.

[Q_Orders_Crosstab] SQL:
TRANSFORM First(Q_Orders.Title) AS FirstOfTitle
SELECT Q_Orders.Line, Q_Orders.Customer
FROM Q_Orders
GROUP BY Q_Orders.Line, Q_Orders.Customer
PIVOT Q_Orders.Header;

[Q_Orders_Crosstab] Datasheet View:
Line Customer Col_1 Col_2 Col_3
------- -------- --------- --------- ---------
Line 1: John Doe Order # 1 Order # 2 Order # 3
Line 2: Jane Doe Order # 1 Order # 2

There are other ways to do this, without using a Crosstab, but I think
this will do what you asked for. (Warning: If your data include an
order # 4 or others, you'll wind up with additional columns in the
Crosstab.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
Click to expand...
 
V

Vincent Johns

Fred said:
Thanks Vincent. I see what you are doing and I will see how it works. The
"Blank" was just a place holder for an empty Cell.
Click to expand...

That's what I'd assumed.

You can edit any of these Queries in Query Design View. Good luck.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 

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

Need help transposing multiple columns in a query 0
Monthly Data Analysis 2
Sort by number of unique entries in a datafield 3
Query to find the most recent record for each duplicate record 3
Select Top Query 2
Help with Query (or Macro??) 3
how to output Access table to a text document? 1
How to get this table updated with a result from another query 2

Top