Results 1 to 12 of 12

joining table and group by id

  1. #1
    atzdgreat is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    5

    joining table and group by id

    Click image for larger version. 

Name:	Capture.JPG 
Views:	20 
Size:	19.7 KB 
ID:	39722

    Code:
    Set rs = db.OpenRecordset("SELECT Testing.ID, SUM(Testing.awarded) AS SUBTotal, SUM(Testing1.awarded) AS SUBTotal1 FROM Testing LEFT JOIN Testing1 ON Testing.ID = Testing1.ID  GROUP BY Testing.ID ", dbOpenSnapshot, dbOptimistic)
    when i run the result.



    only Apple with 7 and Orange 12. but banana and grape didnt display.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    Are the fruit fields the ID fields? Joining on these fields will result in a Cartesian relationship. Don't see how the number results you indicated are possible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,103
    ?? Those aren't pics of table joins in query design. I see a join in the sql, so how is that a Cartesian situation?
    I suspect the issue is the Group By being on the right side of the join (table that doesn't have the missing records), but I'm no sql expert (regrettably).
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    (humor about talking to yourself apparently was too much for some)

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    Not sure if this is what you're looking for, but this was my interpretation of your query SQL and your wanting to see banana and grape in result. I renamed your tables since they overlapped with some sample tables I had in my database.

    Code:
    select id,sum(subtotal) as MyTotal
    from
    (
    SELECT Testinga.ID
    , SUM(Testinga.awarded) AS SUBTotal
    FROM TestingA
    group by testinga.id
     Union
    SELECT Testingb.ID
    , SUM(Testingb.awarded) AS SUBTotal
    FROM Testingb 
    group by testingb.id
    )
    group by ID
    id MyTotal
    apple $7.00
    banana $3.00
    grape $5.00
    orange $12.00

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    The JOIN would have Cartesian result because there are multiple same fruit joining to multiple same fruit. For a SELECT without grouping, 2 Apple records in each table will result in 4 Apple records in query, 3x3 = 9 Orange.
    ID Testing.Awarded Testing1.Awarded
    apple 1 2
    apple
    1 2
    apple
    2 2
    apple
    2 2
    orange 1 1
    orange
    1 4
    orange
    1 3
    orange
    1 1
    orange
    1 4
    orange
    1 3
    orange
    2 1
    orange
    2 4
    orange
    2 3
    banana 3

    Then the Sum() on each produces:
    ID SUBTotal SUBTotal1
    apple 6 8
    banana 3
    orange 12 24

    I replicated your tables as posted and used your SQL.

    So, if Orange's suggestion is not what you are looking for, need to provide more info.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,103
    The JOIN would have Cartesian result because there are multiple same fruit joining to multiple same fruit.
    well, you may be right about the results you got, but I don't see how this is a true Cartesian product (result is a misnomer) because a Cartesian product is what you get when you don't join any tables that are involve. Clearly there is a join in the sample sql. To calculate a Cartesian product you multiply the record count in one table by the record count in another table because there is no join at all. Each record in one table has a corresponding record with each other record in the other table. In the tables shown, that would be 6x6 = 36. You show 14 records - because there is a join.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    There is still a Cartesian association (at least looks like same concept to me, or call it whatever you want) WITHIN EACH FRUIT GROUP. The point is, joining tables on those fields is not making sense.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,103
    Never heard of a Cartesian "association" much less by group.
    Block caps means you win

  9. #9
    atzdgreat is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Hi thank u for the reply and yes fruits are my id. This is just a sample and was thinkin of grouping and joining since i am new with programming. What i want to display in listview is:

    ID Subtotal Subtotal1
    Apple 3 4
    Orange 4 8
    Banana 3 0
    Grape 0 5


    Again thank u for your reply

  10. #10
    atzdgreat is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Hi sorry for the late reply and thanks. Yes fruits are my id. This is just a test for me on how to do joining and groupig at the same time.. my apology if i didnt include what i want to display. It should be like this:

    ID Subtotal Subtotal1
    Apple 3 4
    Orange 4 8
    Banana 3 0
    Grape 0 5

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    atzdgreat,

    I have approved your latest 2 posts to make them visible to readers.
    It is unclear what your vba code, the embedded sql and the graphic in your original post was intended to do.
    Without some simple description of the context of your basic issue responders could only guess. I recommend you do some research on database concepts, design and normalization and also work through some tutorials.
    Many coming to Access(database) from experience in/with Excel(spreadsheet) have difficulty with concepts and approaches because of the differences in the underlying object models.
    Good luck.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    What do you mean by 'listview' - a listbox?

    You have two ways to accomplish.

    First one

    1. have a dataset with all possible fruit - a 'master' table; if you don't have one it can be created with UNION:
    SELECT ID FROM Testing UNION ID FROM Testing1;

    2. build two aggregate queries that summarize each table

    3. join the two aggregate queries to 'master', I think it will be a LEFT JOIN for each link

    Second one

    1. start with a UNION query
    SELECT 1 AS Source, ID, Awarded FROM Testing
    UNION ALL SELECT 2, ID, Awarded FROM Testing1;

    2. use that query as source for a CROSSTAB
    or emulate a CROSSTAB
    SELECT ID, Sum(IIf(Source=1,Awarded,Null)) AS SubTotal, Sum(IIf(Source=2,Awarded,Null)) AS SubTotal1 FROM qryUNION GROUP BY ID;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Joining table?
    By elliemac in forum Database Design
    Replies: 7
    Last Post: 05-04-2019, 02:13 AM
  2. Replies: 5
    Last Post: 11-28-2016, 08:18 AM
  3. Replies: 1
    Last Post: 07-01-2014, 01:41 PM
  4. Replies: 3
    Last Post: 09-16-2013, 01:05 PM
  5. Replies: 5
    Last Post: 08-06-2013, 01:32 PM

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
  •  
Tech Forums: Microsoft Office Forums