Solved Query shows not correct values (1 Viewer)

silentwolf

Active member
Local time
Today, 04:56
Joined
Jun 12, 2009
Messages
545
Hi guys,
unfortunatelly I would need some help with a Query.

Attached the Query in Design View...

And this is the Query in SQL
Code:
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
 

Attachments

  • qryArticles.JPG
    qryArticles.JPG
    70.5 KB · Views: 198

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,357
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

Active member
Local time
Today, 04:56
Joined
Jun 12, 2009
Messages
545
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

Active member
Local time
Today, 04:56
Joined
Jun 12, 2009
Messages
545
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
BaseIIDArticleNrPriceSupIDRefManuIDRef
1123110.8811
1123110.8811

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

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,357
You might consider posting a sample db with some test data.
 

silentwolf

Active member
Local time
Today, 04:56
Joined
Jun 12, 2009
Messages
545
Hi,

thanks DBguy )

Here is the file

Many thanks
 

Attachments

  • FD_ArtikelVerwaltungTest_V21.zip
    123.8 KB · Views: 204

bastanu

AWF VIP
Local time
Today, 04:56
Joined
Apr 13, 2010
Messages
1,401
Your problem is the data in the BasePacSupPrices table, you stored the SupplierID from BasePacSuppliers instead of the PK (BPSupIDN).
Screenshot 2022-01-27 102610.png
 

Attachments

  • FD_ArtikelVerwaltungTest_V22.zip
    118.8 KB · Views: 201

silentwolf

Active member
Local time
Today, 04:56
Joined
Jun 12, 2009
Messages
545
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,357
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

silentwolf

Active member
Local time
Today, 04:56
Joined
Jun 12, 2009
Messages
545
Thank you DBGuy!
And also thanks for your input!

Much appreciated!
 

Users who are viewing this thread

Top Bottom