access report

[Dell XPS]

ok, i need a way to make an invoice. i have a products table with the number the name and the price of all my products and need to add only the products I want on the report, not all of them but more than 1 ......

[hopalongcassidy]

Create a table called Inventory that contains all of your products with a field called "ProdId". Create a table called DesiredProds that contains the product id's you want to select.

Use the following SQL statement to get what you want.

SELECT * FROM Inventory INNER JOIN DesiredProds ON Inventory.ProdId = DesiredProds.ProdId

Make sure that there are no duplicate ProdId's in either table or you will get duplicate records in your output.

Hoppy

[Dell XPS]

ok i have managed to do that by using VB coding to create a mulptiple value parameter query! Now I need to display and the name of the customer buying the products but access does not let me use 2 queries on one report because the tables are not connected it says !!!

Customers Table:
Customer Id:
Name
Surname:
Telephone:

Products table:
Product Code:
Description
Price:

[hopalongcassidy]

Thinking about your original question, it really seem like what you want to do is to have two tables that represent your invoices. One table (InvoiceHeader) that contains one row for each Invoice (each row containing the Id of the Customer to whom you sold products) and a (LineItem) table containing one row for each item you sold on each invoice. The LineItem table would have the InvoiceNumber, the ProductId, the Quantity and the Price and maybe some other stuff.

What it seems like you want to do is to search the LineItem table for the desired products and for each one found, find the CustomerId in the InvoiceHeader table using the InvoiceNumber in the LineItem table. From there, find the CustomerName in the CustomerTable using the CustomerId in the InvoiceHeader table.

Do I understand what you are looking for or am I off track?

Hoppy

[Dell XPS]

ok i have managed to do that by using a subreport, in my invopice report, based on my customer query....

[Dell XPS]

Now that i have managed to do these things I need a way to input the quantity and the discount(if any) for each product on my invoice.....

[hopalongcassidy]

If you want to be able to type in this information, you will need to create a form. The form should contain information to orient the person entering the information as well as fields into which the information can be entered. Also, you will need to have a "Submit" and a "Cancel" button.

Put the logic to process the input in the Submit_OnClick() method. If you are using MS Office - Professional Edition, you can generate the this method from the properties window by clicking on the "On Click" event, then click on the "...", then click on "Code Builder", then click "OK".