Problem with a simple query with supplies from a certain period to a certain plant

[Jarek]

hello everybody,



my name is Jarek from Poland in central Europe.

this is my firt post with a problem that I cannot solve.

there are 2 tables in the database.
one with prices for every single part number in each plant and the second one with the deliveries.

I need to make a query where I have a specific part number with a specific plant and the deliveries according to the part number and plant.
now in the query there are not only duplicates values that are wrong but also for a Plant SouthEast and the part number 49349 which is on the table price list I should receive no supplies however I get values.

thank you in advance and best regards.

[CJ_London]

you also need to join on Plant to Plant.

Note that your supplies table is not normalised - it should be

ID
Plant
PartNimber
supplyMonth
supplyAmount

[orange]

As Ajax advised, you should normalize your tables. It also seems to that there should be more entities (tables).
Do you Deliver a Part from a plant to a Customer?
Do you build/manufacture the Parts? Supplier(s)?

Best practice:

Get a big picture of the area under review
Identify the things
Identify how they relate to one another
Build a data model
Test the model with some test data
Adjust until the model works based on your test data and scenarios.
Use the model to create your database.

[Jarek]

thank you both for your remarks.

works really well.

didn't understand how table with supplies should be normalised.

supplyMonth
supplyAmount

shouldn't work like in excel?

[CJ_London]

shouldn't work like in excel?

Nothing like excel

for your table if you want to multiply quantity by price you will need three calculations - and every time you add another month you will also need to add another calculation. Plus perhaps another for year to date which will have to be changed each time you add a month

if the data is normalised, you only need one calculation.

With excel data is stored and presented at the same time. In databases, data is stored in tables and presented in queries/forms/reports. i.e. excel data is short and wide' in databases it is stored 'tall and narrow'.

excel uses ranges, databases have tables and fields.

google 'normalisation' to find out more. If you don't normalise then at some point you will find that you cannot do something.

[Jarek]

thank you for your help. I will try to investigate more about it for sure.