Do I need a subquery?

[bengiomanni]

I have a table called FundLedger. The primary key field is FundLedgerUID. Other fields I am using are AccountID, GiftReceivedDt, and DeductibleDonationAmt.

I am trying to write a query that will give me the FundLedgerUID for the earliest GiftReceivedDt for each AccountID. The reason I have to do this is because if I sort the table by GiftReceivedDt, the values in FundLedgerUID are not in the same order, meaning that it seems that some data must have been inserted out of sequence.

I don't know if I need a subquery or some kind of calculated field in the query. Part of me says this is much simpler than I am making it. I am new to very advanced Access features. Please help!

What I am ultimately trying to do is have a query that gives me the date of each AccountIDs first gift and the amount and the most recent gift and the amount. I was simply using a totals query at first and then I realized that for some of the accounts the query was not giving me the earliest date or the earliest gift amount because it was deciding this based on the primary key, which is sometimes out of sequence when compared to the date.

 

[Brianwarnock]

A simple totals query with the two fields fundledgeruid and giftrecieveddt, Groupby on the first and min for the date, thus you now have the first date for each fundledgeruid. This query is joined back to the table on these two fields to extract the rest of the data you require.

Brian

 

[bengiomanni]

Thanks for the replay Brianwarnock. Could you explain the next steps for me? I'm rather new. I did the totals query but I don't know what to do with it now. Do you mean that I should join the totals query on BOTH the FundledgerUID and GiftReceivedDt fields? If so, then what?

 

[Brianwarnock]

Yes that is correct, you can now select the other fields that you want from the table.
Joining the query on the ID and date fields to the table means that only the records with the minimum, that is first date will be selected.

You only run the second query the other is run under the covers by the system , actually it compiles both into one set of code just as if you had used a sub query.

Brian

 

[bengiomanni]

Ok. I made the totals query, qryFundLedgerUID

SELECT dbo_FundLedger.FundLedgerUID, Min(dbo_FundLedger.GiftReceivedDt) AS FirstDate
FROM dbo_FundLedger
GROUP BY dbo_FundLedger.FundLedgerUID
ORDER BY Min(dbo_FundLedger.GiftReceivedDt);

Then I have a new query where I have joined that to the original table, FundLedger, on both FundLedgerUID = FundLedgerUID and FirstDate = GiftReceivedDt.

I have selected AccountID, GiftReceivedDt, and DeductibleDonationAmt from the FundLedger table in my new query. When I run it for a specific AccountID, I still get all of his records. I just want the very first one.

 

[Brianwarnock]

I don't know why that should be so, can you post the SQL just in case I can spot something.

Brian

 

[bengiomanni]

Here is the SQL for the new query that combines the original table and the other query I made.


SELECT dbo_FundLedger.AccountID, dbo_FundLedger.GiftReceivedDt, dbo_FundLedger.DeductibleDonationAmt
FROM dbo_FundLedger INNER JOIN qryFundLedgerUID ON (qryFundLedgerUID.FirstDate = dbo_FundLedger.GiftReceivedDt) AND (dbo_FundLedger.FundLedgerUID = qryFundLedgerUID.FundLedgerUID)
ORDER BY dbo_FundLedger.AccountID, dbo_FundLedger.GiftReceivedDt;

The problem I see is that the first totals query that I make does not filter the records down at all because I am showing the FundLedgerID in the aggregate totals. It can't group them down because every one of them is unique. So I am not seeing how that will filter the table in this new query.

 

[bengiomanni]

Ok. I think I have it now. I used AccounID instead of FundledgerUID. Here is my SQL for both things now.

SELECT dbo_FundLedger.AccountID, Min(dbo_FundLedger.GiftReceivedDt) AS FirstDate
FROM dbo_FundLedger
GROUP BY dbo_FundLedger.AccountID
ORDER BY Min(dbo_FundLedger.GiftReceivedDt);


SELECT dbo_FundLedger.AccountID, dbo_FundLedger.GiftReceivedDt, dbo_FundLedger.DeductibleDonationAmt
FROM qryFundLedgerUID INNER JOIN dbo_FundLedger ON (qryFundLedgerUID.FirstDate = dbo_FundLedger.GiftReceivedDt) AND (qryFundLedgerUID.AccountID = dbo_FundLedger.AccountID)
ORDER BY dbo_FundLedger.AccountID;

Now I just get one record for each AccountID, and so far it looks like it solved my problem. My only concern is the possibility of an AccountID having two records with the same GiftReceivedDt that are both the earliest date. I think that is a pretty slim chance, but I assume in this situation that Access would use the primary key to make the final decision of which one to take? That's probably fine to leave it there, if so, but I would like to hear your thoughts.

 

[Brianwarnock]

Sorry when I wrote my post I confused AccountID and FundLedgerUID, you have of course corrected that.

If you can have more than 1 record with the same date for the same AccountID then you will Select all of those records that match across the join.
You will need to insert yet another query, if it doesn't matter which of those records is chosen I would use First against the FundLedgerUID, this function operates at field level within a Group

So
SELECT First(dbo_FundLedger.FundLedgerUID) As requiredUID FROM qryFundLedgerUID INNER JOIN dbo_FundLedger ON (qryFundLedgerUID.FirstDate = dbo_FundLedger.GiftReceivedDt) AND (qryFundLedgerUID.AccountID = dbo_FundLedger.AccountID)
Groupby dbo_FundLedger.AccountID,dbo_FundLedger.GiftReceivedDt ;

This now gives you the unique FundLedgerUID which selects the records you want and can be joined back to the table for the final query.


Brian

Edit the Forum wont let me recmove the space that it has inserted, it does that crazy thing occasionally

 

[Brianwarnock]

Sorry when I wrote my post I confused AccountID and FundLedgerUID, you have of course corrected that.

If you can have more than 1 record with the same date for the same AccountID then you will Select all of those records that match across the join.
You will need to insert yet another query, if it doesn't matter which of those records is chosen I would use First against the FundLedgerUID, this function operates at field level within a Group

So

SELECT First(dbo_FundLedger.FundLedgerUID) As requiredUID FROM qryFundLedgerUID INNER JOIN dbo_FundLedger ON (qryFundLedgerUID.FirstDate = dbo_FundLedger.GiftReceivedDt) AND (qryFundLedgerUID.AccountID = dbo_FundLedger.AccountID)
Groupby dbo_FundLedger.AccountID,dbo_FundLedger.GiftReceivedDt ;

This now gives you the unique FundLedgerUID which selects the records you want and can be joined back to the table for the final query.


Brian