Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2017
    Posts
    4

    Unanswered: MS Access Join problemq

    Hi. First month user with Access. Thanks for looking at my post. I have a table with every date from 2016 to today. I need to run a query that returns every date in that table, and the sum of transactions from a linked transaction table (joined by date), regardless of whether or not there were transactions for each date. My query returns only days on which there were transactions. Can someone offer advice? Thanks.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    you want an OUTER JOIN.
    dbl-click the join line betwen the tables,
    set it for ALL RECORDS in tDateTbl and some in the tTransaction data table
    bring down the tDateTbl.Date and tTrans.Date
    the missing items will be null.

  3. #3
    Join Date
    Jan 2017
    Posts
    4
    Thanks for getting back to me. I'm doing exactly that (Join Option 2, "include all from date table, and those that match from transaction table) and yet it only returns a dataset of those dates where there are summed amounts of transactions. Dates where there are no transactions for my "Where" statement (specific transaction types) disappear. I'm perplexed.

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    If you're aggregating the results, the standard outer join won't work quite as expected. Try this:
    Code:
    SELECT t1.DateField, Sum(Nz(t2.TransactionAmount, 0)) AS DayTransactions 
    FROM tDateTbl AS t1 LEFT JOIN tTransaction AS t2 on t1.DateField = t2.DateField
    ORDER BY t1.DateField
    GROUP BY t1.DateField;
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  5. #5
    Join Date
    Jan 2017
    Posts
    4
    Thanks so much for your help. I've tried to incorporate your suggestion but I'm still having issues.
    Dates table is called Set_Dates and contains nothing but date field with no missing days for past year
    Transaction table is called Data and contains many transactions for each day. What I'm trying to accomplish is for certain types of transactions that don't occur on a daily basis, I want the sum amount of those transactions for the day on which they occurred. I want my result to include ALL dates in the Set_Dates table, even if there were no transactions for the specific product types you'll see I specify in my "Having" statement.
    Two things are happening.... 1. Dates are dropped when there are not transactions that occur on a given day, and 2. when there are multiple transactions on a given day, they are not summed together, but rather listed separately for that date. I appreciate your help greatly and I think I'm close, just missing something minor I hope.
    Thanks so much for your insight.

    Here's the SQL:

    SELECT Set_Dates.Adjudication_Complete, Format([data]![Adjudication_Complete],"yyyy") AS [Year], Format([data]![Adjudication_Complete],"mmmm") AS [Month], Sum(Nz(Data.TP_Total_Amt_Paid,0)) AS TransSum
    FROM Set_Dates LEFT JOIN Data ON Set_Dates.Adjudication_Complete = Data.Adjudication_Complete
    WHERE (((Data.Billing_Method)<>"MAN"))
    GROUP BY Set_Dates.Adjudication_Complete, Format([data]![Adjudication_Complete],"yyyy"), Format([data]![Adjudication_Complete],"mmmm"), Data.Product
    HAVING (((Data.Product) Like "epclu" Or (Data.Product) Like "har*" Or (Data.Product) Like "sovald*" Or (Data.Product) Like "Viek*"));

  6. #6
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Ah. This is another feature of the left join. If you specify any criteria on the right hand side of the join (the one for which you expect to see nulls), you immediately exclude nulls from the returned data. There are two ways around this:
    • Write a query for the right hand side of the join that does all the required selection first, and use this in the left join
    • Write your criteria to allow for nulls

    I would also advise you to aggregate on values from the left hand side of the join. For example:
    Code:
    qryData1
    SELECT * FROM [Data] WHERE [Billing_Method] <> 'MAN' AND ([Product] LIKE '*epclu*' OR [Product] LIKE 'har*' OR [Product] LIKE 'sovald* OR [Product] LIKE 'Viek*';
    
    Totals by date:
    SELECT sd.Adjudication_Complete, Format(sd.Adjudication_Complete, 'yyyy') AS 'Year', Format(sd.Adjudication_Complete, 'mmmm') AS 'Month', Nz(qd.[Product], '-') AS TransProd, Sum(Nz(qd.TP_Total_Amt_Paid, 0)) AS TransSum
    FROM Set_Dates AS sd LEFT JOIN qryData1 AS qd ON sd.Adjudication_Complete = qd.Adjudication_Complete
    GROUP BY sd.Adjudication_Complete, Format(sd.Adjudication_Complete, 'yyyy'), Format(sd.Adjudication_Complete, 'mmmm'), Nz(qd.[Product], '-');
    Some more observations:
    • In the code that you posted, you're grouping by a field that you haven't selected. I don't think that Access will let you do that.
    • Your code will be easier to write if you avoid using reserved words for table and field names and aliases (e.g. "Data", "Year", "Product")
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  7. #7
    Join Date
    Jan 2017
    Posts
    4
    I got it to work. Could never have done this without your help. Learned a lot in the process which should help me with future requirements.
    Thanks so much!!

  8. #8
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

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
  •