Results 1 to 6 of 6
  1. #1
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352

    Angry Using Query to Return a Calculation...Returning Way too Many Values

    I am using a query to conduct a simple calculation from my tables. Here is SQL statement:
    Code:
    SELECT [tbl_Cell].[TargetSkids]*[tbl_Cell].[Canisters_Skid]*[tbl_DataEntry].[DownTime] AS CansLost
    FROM tbl_Cell, tbl_DataEntry;
    The query should only be returning 9 records. But for whatever reason I get over 60 results. Mixed in there is the correct values I seek, but I am not sure where these other numbers are originating from. Here is some screenshots to help you, reader, better understand my problem.



    Click image for larger version. 

Name:	calc pt 1.PNG 
Views:	7 
Size:	13.5 KB 
ID:	21238First part, should be multiplying these two columns by this one >Click image for larger version. 

Name:	calc pt 1.1.PNG 
Views:	7 
Size:	6.7 KB 
ID:	21239
    My query then looks likes this below. The highlighted records are actual correct values, the rest are 'extras'.
    Click image for larger version. 

Name:	calc pt 1.2.PNG 
Views:	7 
Size:	12.8 KB 
ID:	21240

  2. #2
    knarfreppep is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Location
    Adelaide, Australia
    Posts
    106
    You appear to be doing a join which returns every row from your first table with every row from the second.

    I think you need to specify the joins - e.g. -

  3. #3
    knarfreppep is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Location
    Adelaide, Australia
    Posts
    106
    ..........
    Click image for larger version. 

Name:	pic.jpg 
Views:	6 
Size:	102.2 KB 
ID:	21241

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you don't have a Join defined between the two tables, the results will be a Cartesian product.
    That means every record in Table A will be joined with every record in Table B.
    So if you have 9 records in Table A, and 7 records in Table B, the resulting query will return 63 records (9 times 7).

  5. #5
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Ah, so I joined the two tables on the field "Cell" which they share in common.
    Code:
    SELECT ([tbl_Cell].[TargetSkids]*[tbl_Cell].[Canisters_Skid]*[tbl_DataEntry].[DownTime]) AS CansLostFROM tbl_Cell
    INNER JOIN tbl_DataEntry
    ON tbl_Cell.Cell = tbl_DataEntry.Cell;
    Still returns some incorrect answers, but volume of results has drastically improved (63 down to 16).

    However, "Cell" is not a primary key...if it were would this help? My tbl_Cell is the first image in my OP. I can't have the specific cell field be the PK since it repeats, but I can have the record be a Pk per Cell + product combo.

  6. #6
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    ^^ I believe this is what my problem is..
    I have been asked to make the db off of some excel spreadsheets that have old data i am entering right now to test some things..and in the table i am joining it to (tbl_DataEntry) they do not include the Cell and Product combo that I would need for a PK-FK relationship.

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

Similar Threads

  1. Query not returning all values
    By whitelexi in forum Queries
    Replies: 12
    Last Post: 09-06-2014, 11:40 PM
  2. Query to return values
    By narendrabr in forum Queries
    Replies: 3
    Last Post: 01-08-2013, 09:30 AM
  3. Replies: 8
    Last Post: 09-27-2012, 11:25 AM
  4. Query return 0 for null values
    By rachello89 in forum Access
    Replies: 4
    Last Post: 02-23-2012, 08:38 AM
  5. Query not returning null values
    By janelgirl in forum Access
    Replies: 5
    Last Post: 10-11-2011, 10:31 AM

Tags for this Thread

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