Creating an invoice (1 Viewer)

[pixiedust]

Hi, I have what is probably a familiar set up. I have the following tables

Product
OrderLine
Orders
Customer

I cant post an image of the relationships at the moment due to my post count, but a Product can have one to many Orderlines. An Orderline can have many to one Orders. And Orders can have many to one Customers.

So each of these tables has data. The order table has a field which determines if an invoice has been dispatched. I need to place a button somewhere which when clicked, will get all orders which do not have an invoice dispatched so it can create an invoice for them.

So I have gone into Query Design, and added my tables and the data I need to display in the invoice. For the invoice dispatched, I have set the criteria to false.

The problem is, when I execute this query, it shows me all the rows in OrderLine which do not have an invoice dispatched. I just need it to show the orders which need an invoice dispatched, and not duplicate the orders which have more than one product in them.

So at the moment, because Order 1 has two products in it, Order 1 is being listed twice by my query. So is there any way to make the listings unique, or another way to do this?

I hope I have not made this too confusing, I was starting to get lost writing this!

Thanks

 

[jdraw]

You're going to have to show us your query sql and/or you tables and relationships.
There are many posts in this and other forums regarding Customers and Orders.

You can post jpgs etc if you put them in zip format.



Here is a free generic data model related to Customers and Orders and Invoices.

Good luck.

 

[pixiedust]

Hi, I have attached the relationships between my tables. I am not allowed to use an invoice table for this so it needs to be done through a query. Not to sure how to show the SQL code as I have done it through the query builder.

Thanks

 

[jdraw]

I am not allowed to use an invoice table for this so it needs to be done through a query.

So, is this homework or an assignment?

First, Access does not like embedded spaces in field names (demands additional syntax). You will do yourself a great favor by using only alphanumeric and "_"(underscore) characters.

Also, as you have done, always include Quantity and AgreedUponPrice at the line item level. If you only use the Price in the Prduct table to calculate Prices, you will find that historic records change when you change the Price (unless you handle currentProductPrice in some manner).

Often, but still debated, you calculate current stock levels based on transactions and do not store the level in a table. If you search various forums, you will see the debate and the various proponents/rationale.

You indicate InvoiceDispatched and PaymentDueDate, as well as Paid. This does not allow for partial payments, but perhaps that is handled in your problem definition.

Same issue with insufficient StockOnHand -- but I'll assume BackOrders and Partial Orders are beyond the scope of the problem/assignment.

For reference you may want to review this article by Allen Browne.

As for the SQL of a query:
In the query designer, select SQL view; copy the code from the window nd paste it between [c o d e ] and [ / c o d e ] tags ( no spaces in the real tag),

Good luck with your project.

 

[pixiedust]

Thank you for the information, this is just a little task I have been set. I will make amendments to the names so they follow a better standard. The SQL shows up like

SELECT Customer.[Customer ID], Customer.[Company name], Customer.[Address line 1], Order.[Order ID], Order.[Date of order], Order.[Invoice Dispatched], Product.Description, OrderLine.Quantity, OrderLine.[Price paid]
FROM (Customer INNER JOIN Order ON Customer.[Customer ID] = Order.[Customer ID]) INNER JOIN (Product INNER JOIN OrderLine ON Product.[Product ID] = OrderLine.[Product ID]) ON Order.[Order ID] = OrderLine.[Order ID]
WHERE (((Order.[Invoice Dispatched])=False));

Now the problem with this is that when executed, it displays

Customer ID | Company name | Address line 1 | Order ID | Date of order | Invoice Dispatched | Description      | Quantity | Price paid
-------------------------------------------------------------------------------------------------------------------------------
1000          Halfords         34 Alton Drive      1             23/02/2015         No                      Bike                 1            134.99
1000          Halfords         34 Alton Drive      1             23/02/2015         No                      Gloves             1            22.95
1000          Halfords         34 Alton Drive      2             06/04/2015         No                      Wheel                 1            29.34

Now I think this query is correct, the correct data is returned. However, I need to create an invoice for each order. So for the above output, there should be 2 invoices Order ID 1 and Order ID 2. The first invoice should have 2 Products and the second one product.

This is the stage I am stuck on. If I try creating a report with the above query it places everything above into one report.

How would I go about separating it into separate reports for each Order?

Thanks