Results 1 to 7 of 7
  1. #1
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376

    Very Confused with results

    Hello all,

    I am extremely confused by the results I am getting from 2 different queries that should be very similar. I copied the query that is working properly and renamed it, then modified the new query to use the correct table but when I view the results it makes no sense. The first query uses tblM29242 which has 3 records in it, when I view the results of the query it does indeed give me 3 records as expected. The new query uses tblM16086 which has 9 records however when I view the results of the query I get 6426 records when I should be getting 9. I have checked the relationships and it all seems to be correct. The SQL for the queries is shown below. Can someone please let me know what I am missing?

    SELECT DISTINCT tblPartMain.MfrPartNumber, tblPartMain.PartDescription, tblPartMain_1.PartMainID_PK, tblPartMain_1.MfrPartNumber AS Child_Part_Number, tblM29242Rel.Qty, tblPartMain_1.PartDescription AS Child_Part_Description, tblCategories.PartType, tblPartMain_1.SubAssembly, [Work Orders].Qty, [tblM29242Rel]![Qty]*[Work Orders]![Qty] AS [Build Qty], [Work Orders].DHRNumber, [Work Orders].DHRID, Nz([UnitsReceived],0)-Nz([UnitsSold],0)-Nz([UnitsShrinkage],0) AS QtyonHand, tblM29242Rel.Units, [Work Orders].SerialNum, [Inventory Transactions].PurchaseOrderID, tblM29242Rel.DHROrder, tblM29242Rel.MyNotes
    FROM ((tblCategories RIGHT JOIN tblPartMain AS tblPartMain_1 ON tblCategories.ID = tblPartMain_1.CategoryID) RIGHT JOIN tblM29242Rel ON tblPartMain_1.PartMainID_PK = tblM29242Rel.ChildID) INNER JOIN ((tblPartMain LEFT JOIN [Inventory Transactions] ON tblPartMain.PartMainID_PK = [Inventory Transactions].ProductID) LEFT JOIN [Work Orders] ON tblPartMain.PartMainID_PK = [Work Orders].PartID) ON tblM29242Rel.PartMainID_FK = tblPartMain.PartMainID_PK
    ORDER BY tblM29242Rel.DHROrder;


    SELECT DISTINCT tblPartMain.MfrPartNumber, tblPartMain.PartDescription, tblPartMain_1.PartMainID_PK, tblPartMain_1.MfrPartNumber AS Child_Part_Number, tblM16086Rel.Qty, tblPartMain_1.PartDescription AS Child_Part_Description, tblCategories.PartType, tblPartMain_1.SubAssembly, [Work Orders].Qty, [tblM16086Rel]![Qty]*[Work Orders]![Qty] AS [Build Qty], [Work Orders].DHRNumber, [Work Orders].DHRID, Nz([UnitsReceived],0)-Nz([UnitsSold],0)-Nz([UnitsShrinkage],0) AS QtyonHand, tblM16086Rel.Units, [Work Orders].SerialNum, [Inventory Transactions].PurchaseOrderID, tblM16086Rel.DHROrder, tblM16086Rel.MyNotes
    FROM ((tblCategories RIGHT JOIN tblPartMain AS tblPartMain_1 ON tblCategories.ID = tblPartMain_1.CategoryID) RIGHT JOIN tblM16086Rel ON tblPartMain_1.PartMainID_PK = tblM16086Rel.ChildID) INNER JOIN ((tblPartMain LEFT JOIN [Inventory Transactions] ON tblPartMain.PartMainID_PK = [Inventory Transactions].ProductID) LEFT JOIN [Work Orders] ON tblPartMain.PartMainID_PK = [Work Orders].PartID) ON tblM16086Rel.PartMainID_FK = tblPartMain.PartMainID_PK
    ORDER BY tblM16086Rel.DHROrder;

    I have looked at this both in design view and SQL view until I am crosseyed I just can't seem to find out what I am missing. Something stupid I am sure.



    Thanks
    Dave

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure why you think the number of records is related to the table you changed. difficult to see with your mix of left and right joins. I can confirm the two sql's are the same bar the change of table name.

    Suggest make all joins Left joins - perhaps it will become more apparent but from what I can see the number of records returned should be related to tblPartMain since (I think) this is the table you want all records returned.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I formatted your second SQL with Poor SQL for readability.
    Code:
    SELECT DISTINCT tblPartMain.MfrPartNumber
    	,tblPartMain.PartDescription
    	,tblPartMain_1.PartMainID_PK
    	,tblPartMain_1.MfrPartNumber AS Child_Part_Number
    	,tblM16086Rel.Qty
    	,tblPartMain_1.PartDescription AS Child_Part_Description
    	,tblCategories.PartType
    	,tblPartMain_1.SubAssembly
    	,[Work Orders].Qty
    	,[tblM16086Rel] ! [Qty] * [Work Orders] ! [Qty] AS [Build Qty]
    	,[Work Orders].DHRNumber
    	,[Work Orders].DHRID
    	,Nz([UnitsReceived], 0) - Nz([UnitsSold], 0) - Nz([UnitsShrinkage], 0) AS QtyonHand
    	,tblM16086Rel.Units
    	,[Work Orders].SerialNum
    	,[Inventory Transactions].PurchaseOrderID
    	,tblM16086Rel.DHROrder
    	,tblM16086Rel.MyNotes
    FROM (
    	(
    		tblCategories RIGHT JOIN tblPartMain AS tblPartMain_1 ON tblCategories.ID = tblPartMain_1.CategoryID
    		) RIGHT JOIN tblM16086Rel ON tblPartMain_1.PartMainID_PK = tblM16086Rel.ChildID
    	)
    INNER JOIN (
    	(
    		tblPartMain LEFT JOIN [Inventory Transactions] ON tblPartMain.PartMainID_PK = [Inventory Transactions].ProductID
    		) LEFT JOIN [Work Orders] ON tblPartMain.PartMainID_PK = [Work Orders].PartID
    	) ON tblM16086Rel.PartMainID_FK = tblPartMain.PartMainID_PK
    ORDER BY tblM16086Rel.DHROrder;
    This doesn't look to be correct sql syntax
    ,[tblM16086Rel] ! [Qty] * [Work Orders] ! [Qty] AS [Build Qty]

  4. #4
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    I don't know SQL at all, both were copied from the designer in SQL view. I will be trying to create smaller queries and use them in the final query to see what I get. Like I said, the first gives the expected results, the second isn't even close.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dave,

    Is this a database you can post? Or can you provide a slimmed down version with enough records to highlight the problem? Also, would need some instructions --an overview of the purpose of the query; how to get to get to the specific part of the application.

  6. #6
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    I rebuilt the query into smaller pieces and got what I expected, now I will try to copy that, change out the one table for other parts and see how this works out.

    Orange,

    I wish I could post it but I can't. I know there are tons of ways it could be improved, the one big thing right now is the time it takes for 1 query to run which this exercise is going to speed up drastically. Unfortunately for me it involves making about 30 or so specific queries, 1 for each part, then changing code to call each appropriate one which I am using a Case Select for, which someone here help me with a week or so ago.

    I have got to learn SQL like you folks know it but I don't use it enough if that makes sense, maybe if I knew it better I would use it more, I just use the designer.

    Thanks

    Dave

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    OK. But I have difficulty understanding the it involves making about 30 or so specific queries, 1 for each part, then changing code to call each appropriate one which I am using a Case Select.
    In order to get you any focused help/advice on that, readers need to see table design, some data... Even plain English description of the issue. Without more info, we're only guessing.

    The sql you posted earlier involves several tables and fields and many joins -- and is not readily understood without more contextual help.
    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. I am confused please help me
    By pedjvak in forum Forms
    Replies: 5
    Last Post: 04-20-2013, 02:13 AM
  2. very confused about relationships
    By devxweb in forum Access
    Replies: 3
    Last Post: 12-20-2012, 06:47 AM
  3. I am a bit confused by this one
    By wubbit in forum Access
    Replies: 7
    Last Post: 05-15-2012, 03:18 PM
  4. Confused!!!
    By mkc80 in forum Access
    Replies: 1
    Last Post: 05-11-2012, 04:39 PM
  5. Just Confused
    By BigCat in forum Access
    Replies: 1
    Last Post: 05-09-2011, 12:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums