How to create a query that combines multiple rows into one and...

[Simon Bernard]

Hi there

I am trying to create a database that generates delivery notes. I have an excel spreadsheet linked to the database. From the linked information I need to be able to create a delivery note. But in the table, there are multiple instances of an order number.

Order_No Branch Voucher Code
112 44 5
112 44 10
112 44 20

How can I create a report/delivery note so the data is all on one row...

Order No Branch Voucher Code 1 Voucher Code 2 ...
112 44 5 10 20


I really need some advice...my deadline is tomorrow :(

Thanks in Advance

Simon

[nico5038]

Hi Simon,

For this we have the cross-table query that will make columns out of the Vouchers.

There's however a nasty side effect, as the content of the column will be changed into the column name. When you have no fixed max of vouchers per order, it's hard to create a report, as you won't know how many fields to place...

Do you know the max ?

Nic;o)

[Rixxe]

Perhaps if you post what you have so far in your DB, people might be able to have a look.
(Would make the solution a lot faster, seeing as you have a deadline.)
Would it not be easier, just to import the data into access, rather than link from excel?

[Simon Bernard]

Reply

Hi guys

Maximum number of voucher types per delivery note is 5.

How do I upload files to this forum? I am new to this unfortunately

Thanks in advance

[Rixxe]

Hi Simon,

http://bytes.com/faq.php?faq=vb3_reading_posting#faq_vb3_attachments

Should help you attach your DB.

[Simon Bernard]

Database Attached

Hi Guys

Please see attached the database that I am working on

Any help would be hugely appriciated.

Some useful things to note

• 1 Branch can order many Products
  
• I want all the items ordered by the branch on the day that they placed the order to be on 1 document (Delivery Note). This in term means the Order No will appear once on the combined Delivery Note

Thanks in Advance

Simon

[nico5038]

Try the next query to see how I forced the Products to appear in columns:

TRANSFORM Max(Order.[Product Code]) AS [MaxOfProduct Code]
SELECT Order.[Order Number], Order.Branch
FROM [Order]
GROUP BY Order.[Order Number], Order.Branch
PIVOT DCount("Date","Order","[Order Number]='" & [Order Number] & "' and [Branch] = '" & [Branch] & "' and [Product Code] <='" & [Product Code] & "'");

Some additional work is needed to get the "fixed" 5 columns, but I won't spoil your fun.

Nico

[Simon Bernard]

Hi Nico,

The code you wrote worked thanks mate.

Another thing, how would I get the Branch Address fields to appear on the same line as the order/branch?

E.g. Order No, Branch, Address 1, Address 2, P1, P2, P3
(P1,2,3) = Product Value 1 etc)

Thanks again mate

Simon

[Simon Bernard]

Hi mate

Thanks for the sending the code...it worked :D

How would I get the Branch Address fields to appear in the same line?

E.g.
Order No, Branch, Address1, Address2, P1, P2, P3

(P1,2,3 - the products associated to the order no)

Thanks again mate...sorry if this question appears twice, I wasnt sure if my last post was sent)

Cheers

Simon

[nico5038]

Just add them with the same value (Header row) as the two existing fields. When they aren't in the Order table, first JOIN the Customer table.

Nico

[Simon Bernard]

Hi Nico

Thanks for your reply

If I wanted to add more fields to the quert for example a No of Vouchers, Number of books, branch address and the Product related fields how would I do this to work in this query?


Sorry to be a pain mate

Cheers

Simon.

[nico5038]

Check the help description for all possibilities.
There's only one field that's possible as a column header and one for the value. Only the row header allows multiple (group by) fields.
Multiple field values will require multiple queries or tricky querying.

Nico