Combining fields?

Hi all

I have a question about a report.

This is my report as it stands.



It's sorted by month, and shows the products that have been ordered that month, and the individual customer orders by each customer.

These fields are pulled from a SalesOrders table. It all works exactly as it needs to at the moment.

But I've recently added a new field to the SalesOrders table - Product2 - as some customers now order two products on one invoice.

So I need to somehow add the Products2 field into this report, ideally so they're combined in the existing Products column.

Anyone know if this is possible?

Thanks, Paul

what you describe is a denormalised structure = bad. what if a buyer wanted to then purchase a third product from you? you can't just keep adding fields to your table. a quick guide to know if you have a denormalised structure can be gleaned from your naming. as soon as you need to do "product1" "product2"... etc, that indicates you should actually have a table to store these in a normalised fashion.

kinda sounds like you almost have it, but let me elaborate: you should have a setup whereby you add ordered items to an invoice - two fields do the trick (orderID and invoiceID) in the extra "junction" table, and no matter how many items any one buyer chooses to purchase on one invoice(or more), you only ever need those two fields.

in my ordering system, this is how i have the structure:

i have a table with all items that can be ordered in tblItems (item name, catalog #, list price, etc)
i have a table with all orders for each item in tblOrders (quanitity, purchaser, etc)
i have a table with all invoices in tblRequisitions (requisition ("invoice") number, date ordered, etc)
i have a table which joins the orders with requisitions in jtblReqOrders (which orders belong to which requisition (or "invoice" if you prefer to call it that - we are on the "receiving" end of orders - i.e., we buy, we don't supply, but this table is the equivalent of what presumably would be in an invoice for a supplier, i expect))

the relationships are structured as shown below, just so you can visualse what i mean. i can add any number of orders to an invoice, and this makes grouping and sorting a real easy task :



(edit: just so you know, the "invoiceNum" field in my tblOrders is to store external invoice number that come back to us from suppliers - what sometimes happens with our purchasing department, is that we are FIRST sent an invoice, THEN we put that item into the ordering system. weird, i know, but somehow it works...?)

nice job wik. =)

Thanks for your help

I'll have a go at amending my structure.