Solved Query shows not correct values

[silentwolf]

Hi guys,
unfortunatelly I would need some help with a Query.

Attached the Query in Design View...

And this is the Query in SQL

SELECT BaseItems.BaseIID, BasePacs.PackageContent, Units.Description, BaseItems.BaseCIDRef, BaseCategories.Description, Manufacturer.ManufacturerName, ItemCategories.Description, BaseItems.ItemCIDRef, BasePacSuppliers.ArticelNr, BasePacSupPrices.BPSupIDRef, Discounts.Discount, BasePacSupPrices.DiscIDRef
FROM Discounts INNER JOIN ((Manufacturer INNER JOIN (PackagingUnits INNER JOIN (Units INNER JOIN (Suppliers INNER JOIN (((BaseCategories INNER JOIN (ItemCategories INNER JOIN BaseItems ON ItemCategories.ItemCID = BaseItems.ItemCIDRef) ON BaseCategories.BaseCID = BaseItems.BaseCIDRef) INNER JOIN BasePacs ON BaseItems.BaseIID = BasePacs.BaseIIDRef) INNER JOIN BasePacSuppliers ON BasePacs.BasePacID = BasePacSuppliers.BasePacsIDRef) ON Suppliers.SupID = BasePacSuppliers.SupIDRef) ON Units.UnitID = BasePacs.UnitsIDRef) ON PackagingUnits.PacUnitIID = BasePacs.PacUnitIDRef) ON Manufacturer.ManuID = BaseItems.ManuIDRef) INNER JOIN BasePacSupPrices ON BasePacSuppliers.BPSupID = BasePacSupPrices.BPSupIDRef) ON Discounts.DiscID = BasePacSupPrices.DiscIDRef;

As I created the query it showed me all the BaseItems from 1 to arount 8000 records correct Result.
But after saving the query and reopen it it is stuck with BaseIID =1
So there are the correct amount of Records 1 to about 8000 but with the BaseIID 1

I am not sure what or why it is doing that?

Can someone please advice?

Hope the naming is now a little better for you?
I still like to differentiate the FK so hope this is not to bad?

Cheers

 

[theDBguy]

Since I didn't see a WHERE clause, I am just going to guess you may have applied a filter while viewing the query and inadvertently saved it. Just a thought...

 

[silentwolf]

Hi,
TheDBguy..

No unfortunatelly not there is no filter at all..(
The same issue was before but then I created the query again from scratch and it seamed to work but after saving it and reopen it again.. there was the same issue once more.

All values are in place so there is also no issue there...

 

[silentwolf]

I looked and it is actually not stuck and only 1 it is showing 1 to 3
the same amount of Manufacturer and Supplier I got in the tables...

So I got three Manufacturers at present in my Manufacturer Table and also the same amount of Suppliers in the Supplier Table.

The Result shows somthing like that

BaseIID

BaseIID	ArticleNr	Price	SupIDRef	ManuIDRef
1	1231	10.88	1	1
1	1231	10.88	1	1

This goes down the hole amount of Records I have in BaseItems for this Manufacturer... so 6000 Items with the exact same result
Then the same with the 2 Manufacturer and the third..
So 2 Manufacturer 1800 Records
And 3 Manufacturer 3 Records

So the total Amount of Records are correct showing but always with jus one Item of that particular Manufacturer.

Hope this is clear?

 

[theDBguy]

You might consider posting a sample db with some test data.

 

[silentwolf]

Hi,

thanks DBguy )

Here is the file

Many thanks

 

[bastanu]

Your problem is the data in the BasePacSupPrices table, you stored the SupplierID from BasePacSuppliers instead of the PK (BPSupIDN).

 

[silentwolf]

Hi bastanu,

ma so many thanks for your help!!!!!!!!!!!
I looked at the structure over and over and did not see it!

The reason for this Issue was that I had to split import the Data but did not realize that there was an Issue.

But now it works like a charme just hope that I am able to update new Prices then easily enough without any problems.

Again many many thanks to you!!

Cheers

 

[bastanu]

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[silentwolf]

Thank you DBGuy!
And also thanks for your input!

Much appreciated!