Query based on Many to Many Relationship

[navi95]

Hi,

I am currently designing an ordering system with a many to many relationship. I have gotten the foundation to work, to take orders, but since an order can compose of several different items or same items but each one modified I have a junction table which records every single item ordered.

I hope this makes sense so far lol. Right.

Now I have made a query which is meant to show this Driver1 took these orders and the results look like this:

Thats great, BUT, I dont want Bestellnumm (sorry, im working on this in german but english is my native language hence why Im asking on an English site) to appear 3 times as its a single order.
So, I need all 3 records to be grouped together AND all the values of Total Price to be summed up too.

Using the "show no duplicates" in the properties of the query didnt help much as in each line, there is a different thing ordered for the same order ID.

Any thoughts?

 

[stopher]

If you want to display on screen then use a form. If you want to output the use a report. Both tools are designed to group and total as you request.

 

[plog]

Please demonstrate your issue with data. Provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you want the query to return when you feed it the data in A.

 

[navi95]

If you want to display on screen then use a form. If you want to output the use a report. Both tools are designed to group and total as you request.

I need this part for the screen so it will be a form. I just need to refine the data being shown by the query.

 

[navi95]

Please demonstrate your issue with data. Provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you want the query to return when you feed it the data in A.

All the fields are in the screenshot I posted up. So basically I want the first 3 lines to be grouped and it should show like this:

Bestellnummer Telefonummer Addresse Ort ..etc.... Total Preis
10 545566 Per London €124
12 602700 Bo Bham €28

 

[stopher]

Ah, so you want to summarise the data and not show the details? So you should google aggregate queries. They are dead easy to do.

 

[navi95]

Ah, so you want to summarise the data and not show the details? So you should google aggregate queries. They are dead easy to do.

Ahh ok, I did see that mentioned somewhere when I was researching for a solution...ill look into it and give you an update

 

[navi95]

Right ok, I followed this guide:
http://www.opengatesw.net/ms-access...Grouping_Data_with_Aggregate_Queries_in_Acess

Now in the example shown, it looks PERFECT for what I need. But... i followed the few simple steps yet it doesnt seem to be working. Looking at his example, his source is "CalculationQuery"....ok so now I made another query based on my original one and it works!

thanks for your guys help

 

[stopher]

Glad to hear you figured it out.