report with subreport ms access 2007

0
votes

I have three table in my database. Customers, which contains detaills of each client, such as name, phone number ... Products, containing detaills of each product.

each time a client requests a product, a new line is inserted in the third table Orders. the table Orders contains the customer id (foreign key), the product id (foreign key) and the quantity desired.

what I'm looking to do is to creat a report based on the Orders table, that shows me for each Client all the Orders that he has made.

I'm working on ms access 2007.

please help me !!!

2
sqlms-access
Have you tried using the report wizards? – Fionnuala
yes I did, but that gave me a main report with a sub report. but in the main report its list all the Customers even if they have no Orders in the Orders table :-( – Pramine

2 Answers

1
votes

Create a query based on the orders table joined to the customer table. Use the query design window to build the query. You can then base your report on the query, using grouping to get the customer details at the top of the group and the prder details as lines within the group. Use the report wizards.

1
votes

Ok I have the solution.

I created a form with this record source:

SELECT
  DISTINCT Costumers.Code, Costumers.Name, Costumers.phone
FROM
  Costumers INNER JOIN Orders
  ON Costumers.ID=Orders.IdCostumer;

then I've created a subreport with this record source:

 SELECT
   [Costumers].[Code],
   [Orders].[Code],
   [Products].[Description],
   [Orders].[Quantity]
 FROM
   Products INNER JOIN
   (Costumers INNER JOIN Orders ON Costumers.ID=Orders.IdCostumer)
   ON Products.ID=Orders.IdOrder;

and that's working the way I want.

thank you for your interest !!! :-)