creating a simple report (newbie)

[Apprentice101]

I have three tables with one to many relationships:
Customer table Products ordered table
Customer ---> prod1
prod2
prod3

customer table locations table
Customer ---> location1
location2
location3

I need to create a report that combines all of my customers with its correponding info on one row and concatenates all of the many corresponding locations into one column and products ordered. For instance

Report
Customer | All Locations | Products Ordered
Penzoil | loc1, loc2, loc | prod1, prod2, prod3,

What is the best appoach to do this with ACCESS? if you have any examples, would you please be so kind to share them?
thanks!
joey_trivia@hotmail.com

 

[fneily]

I see four tables -
tblCustomer tblLocations tblProducts tblJunction
CustomerID LocationID ProductID JunctionID
CustInfo LocatInfo ProdInfo CustomerID
Etc. Etc. Etc. LocationID
etc. etc. etc. ProductID

You have a bunch of many-to-many relationships and to resolve this you create a junction table that has the primary keys of the tables involved PLUS any common data. It has its' own primary key JunctionID.
The tables tblCustomer, tblLocations, tblProducts are all connected to tblJunction by their primary Keys to the corresponding foreign key in the junction table
Then you can run any kind of report you want, one customer, one product, one location, etc. by simply creating a query.

 

[lameid]

You can't really do this in Access with at least a bunch of advanced coding and then it would be really slow.

What You could do is make a report for both locations and products by customer. Then make a third report of just customers. Finally use the first two reports as subreports in the third report. Be sure so specify master and child field properties of both subreports as the customer. This will list all locations and then all products or vice versa. You may want to play with setting columns if you really want the information next to each other.

You make a report a subreport by dragging it from the database window into another report.

I hope this gets you started and is a livable solution.

 

[lameid]

fneily is right too... we cross posted. Your Junction table might be (for example) OrderDetail and would each product ordered at each location and include an order number, which would require an order table. The order table might include total amount billed, billing address etc.

Your 'junction' table might be something completely different too.

 

[dhookom]

I like the main report with two subreports. If that doesn't work, try the generic concatenate function found at faq701-4233.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]