If contains than group under product

[EmileEA]

Hi,

For my work I get weekly data of specific product which I insert in my access database.
I'm a self thought user so I learn by trial and error. However I have one problem.

As example I insert the following in my database.

201713 - Product X - 24
201713 - Product Y - 33
201713 - Product Z -2

201713 is the CYWeek, followed by the product and the units sold.
This all goes well, but since a few weeks the raw data format I get has changed. It got to specific.

I know have:
201713 - Product X Red - 16
201713 - Product X Blue - 3
201713 - Product X Yellow - 5
201713 - Product Y - 33
201713 - Product Z -2

The problem is that product X is now split into different versions. While it should all be addressed under Product X.
Is there a specific way as in If, contains than group under or some sort of formula which I can use?
Because it's now integrated as if it's a different product.

Thanks for your help and I hope it's specific enough

Kind regards,

Emile

 

[Joe4]

I would use a calculated field in your query to bring it back to the more general "Product X" category, and then group based on that and your CYWeek field, and take the sum of units sold.
If you need help coming up with that expression, please give us some real life examples of what they are, and what they should be.
Be sure to include enough different scenarios that we can come up with a formula that will work for ALL entries.