Solved How to combine

[JazzyInPain]

Hi! New tp Access

I've tried everything but I can't seem to be able to combine the same types of slides (types de glissades)

I'm trying to combine the types of slides (types de glissades), which there is 3 types, together for each month...

It should show the number of slides sold for each month and by type, so I dont know how to make it look like this:

mois (Month)	types de glissade (type of slides)	nombre de glissade (number of slides)
6	1	1
6	2	1
6	3	1
9	1	2
9	2	3
9	3	1
...

I've included some pictures if it's not clear

 

[arnelgp]

convert your Total Query to Crosstab query?

 

[plog]

Change the GROUP BY on COMMANDE_Date to WHERE

 

[JazzyInPain]

convert your Total Query to Crosstab query?

We haven't learned that yet so I'm not sure I should do a crosstab even if it would look more comprehensible...

 

[JazzyInPain]

Change the GROUP BY on COMMANDE_Date to WHERE

It worked!
Thank you!

Is there a reason why the where should be put at the date section? I'm trying to make light of it

 

[plog]

Know your clauses. Here's a great tutorial for what each SQL keyword does:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

GROUP BY adds the field to the list of fields that will be make the data unique:

F1, F2, F3
A, 1, Red
A, 7, Blue
B, 1, Purple
B, 1, Red

GROUP BY F1 yields all unique values in F1:

F1
A
B

GROUP BY F1, F2 yields all unique combinations of F1 and F2:

F1, F2
A, 1
A, 7
B, 1

Since there are 2 (B, 1) values, 1 of them goes away when you GROUP BY both F1 and F2

GROUP BY F1, F2, F3 yields all unique values in F1, F2 & F3 combined:
F1, F2, F3
A, 1, Red
A, 7, Blue
B, 1, Purple
B, 1, Red

Since every record is unique when you take into account all 3 fields, none of them go away when you GROUP BY. (A, 1, Red) and (B, 1, Red) seem close, but because they differ on F1, when you GROUP BY on all 3 fields they are distinct record values because their F1 values are different.

So, when you include a Date field like COMMANDE_Date in the GROUP BY every unique date value in that field will most likely go into its own record (especially when you combine it with other fields as shown above). So, even though you weren't showing that field you were still adding it to the list of fields to make your data unique by.

WHERE is just criteria, either an individual record mets it or doesn't. If it does, its data makes it through and gets shown in the results. By changing it to WHERE you removed its values from the list of unique values to make rows out of (GROUP BY) so all those "extra" rows didn't get made in your query.