Show only record with latest date on subform (1 Viewer)

[fibayne]

Hi...cant get my head round this at all ...I have a subform where info about the progress of debt collection on a clients fees is recorded.
The main form (ClientID, ClientName, ClientCode, Manager) has a subform which shows all current o/s debts for that client (this sf is populated from an excel sheet which is imported daily to tblAgedDebtors).
As the manager contacts the client the date and detail of the call are recorded and a combo is selected (cboFeeDue, options Yes, No, InPart,Deferred) this is done on the main form where there is also a combo which lists the current o/s fee note numbers for that client, this too is selected.
As the manager builds up the calls one FeeNote number can have many entries, at the time of wanting to produce the report a fee note can have been any and all of the options in cblFeeDue.......I am trying in vain to build a report that looks at the date of the calls, using the latest date only as the cboFeeDue selection against this date will be the only one relevant I then need to sum the fee note value by cboFeeDue for each and all managers eg. view all those by latest call date and that have been categorised as deferred ....if this make any sense at all and anyone can give me some help it would as always be higely appreciated ...cheers Fi

 

[MrQ]

Hi fibayne,
Presumably, the main form is based on a table as well as your subform? Is it the case that updating cboFeeDue creates a new record?

Q

 

[MSAccessRookie]

Hi...cant get my head round this at all ...I have a subform where info about the progress of debt collection on a clients fees is recorded.
The main form (ClientID, ClientName, ClientCode, Manager) has a subform which shows all current o/s debts for that client (this sf is populated from an excel sheet which is imported daily to tblAgedDebtors).
As the manager contacts the client the date and detail of the call are recorded and a combo is selected (cboFeeDue, options Yes, No, InPart,Deferred) this is done on the main form where there is also a combo which lists the current o/s fee note numbers for that client, this too is selected.
As the manager builds up the calls one FeeNote number can have many entries, at the time of wanting to produce the report a fee note can have been any and all of the options in cblFeeDue.......I am trying in vain to build a report that looks at the date of the calls, using the latest date only as the cboFeeDue selection against this date will be the only one relevant I then need to sum the fee note value by cboFeeDue for each and all managers eg. view all those by latest call date and that have been categorised as deferred ....if this make any sense at all and anyone can give me some help it would as always be higely appreciated ...cheers Fi

You said that you are looking to use records related to the latest date only. If you get the records containing the required information and sort them in date order (Descending), then using the TOP(1) command to find the first record in the recordset might get what you are looking for.

 

[fibayne]

Hi MrQ...yes the form and subform are both based on tables and updating cboFeedue creates a new record ??

 

[fibayne]

Hi MSAccessRookie...since my first post I have created a query which seems to be giving me latest entered record, but as soon as add more fields (from tblAgedDebtors) to the query Max stops working and I get all records ?? so I am thinking it is my relationships that arent correct ??

Here is what I have so far...
SELECT tblFeeCollectable.FeeNoteFNC, Max(tblFeeCollectable.FNCDate) AS MaxDate
FROM tblFeeCollectable
GROUP BY tblFeeCollectable.FeeNoteFNC
HAVING (((tblFeeCollectable.FeeNoteFNC)>0));

what do you think ??? appreciate your help Fi

 

[MSAccessRookie]

Hi MSAccessRookie...since my first post I have created a query which seems to be giving me latest entered record, but as soon as add more fields (from tblAgedDebtors) to the query Max stops working and I get all records ?? so I am thinking it is my relationships that arent correct ??

Here is what I have so far...
SELECT tblFeeCollectable.FeeNoteFNC, Max(tblFeeCollectable.FNCDate) AS MaxDate
FROM tblFeeCollectable
GROUP BY tblFeeCollectable.FeeNoteFNC
HAVING (((tblFeeCollectable.FeeNoteFNC)>0));

what do you think ??? appreciate your help Fi

Properly formatted, that should not be an issue. remember that for each new item in the SELECT section that is not aggregated, you will need another item in the GROUP BY section.

In addition, the JOIN ON fields should create a unique join as well

 

[fibayne]

Hi MSAccesRookie...I have this now but need to add a field from tblClientCompany which is when the Max stops working ?? any ideas ? thaks again...Fi..

SELECT tblAgedDebtors.Amount, Max(qryMaxFNCDate.FeeNoteFNC) AS MaxOfFeeNoteFNC, Max(qryMaxFNCDate.MaxDate) AS MaxOfMaxDate, tblAgedDebtors.Name, tblAgedDebtors.FeeNoteDate, tblAgedDebtors.ClientCode
FROM qryMaxFNCDate INNER JOIN tblAgedDebtors ON qryMaxFNCDate.FeeNoteFNC = tblAgedDebtors.FeeNoteNo
GROUP BY tblAgedDebtors.Amount, tblAgedDebtors.Name, tblAgedDebtors.FeeNoteDate, tblAgedDebtors.ClientCode
HAVING (((tblAgedDebtors.Amount)>0) AND ((Max(qryMaxFNCDate.FeeNoteFNC))>0));

 

[MSAccessRookie]

Hi MSAccesRookie...I have this now but need to add a field from tblClientCompany which is when the Max stops working ?? any ideas ? thaks again...Fi..

SELECT tblAgedDebtors.Amount, Max(qryMaxFNCDate.FeeNoteFNC) AS MaxOfFeeNoteFNC, Max(qryMaxFNCDate.MaxDate) AS MaxOfMaxDate, tblAgedDebtors.Name, tblAgedDebtors.FeeNoteDate, tblAgedDebtors.ClientCode
FROM qryMaxFNCDate INNER JOIN tblAgedDebtors ON qryMaxFNCDate.FeeNoteFNC = tblAgedDebtors.FeeNoteNo
GROUP BY tblAgedDebtors.Amount, tblAgedDebtors.Name, tblAgedDebtors.FeeNoteDate, tblAgedDebtors.ClientCode
HAVING (((tblAgedDebtors.Amount)>0) AND ((Max(qryMaxFNCDate.FeeNoteFNC))>0));

To add more tables, just add more joins. It is a good idea to group them in a way to define the order that the sets are selected.for example:

[B]FROM[/B] [SIZE=4][COLOR=red]([/COLOR][/SIZE][B]qryMaxFNCDate INNER JOIN tblAgedDebtors [/B]
[B]ON qryMaxFNCDate.FeeNoteFNC = tblAgedDebtors.FeeNoteNo[SIZE=4][COLOR=red]) [/COLOR][/SIZE][/B]
[B][SIZE=4][COLOR=red][COLOR=#9932cc]{Some type of JOIN}[/COLOR] tblClientCmpny [/COLOR][/SIZE][/B]
[B][SIZE=4][COLOR=red]ON [COLOR=darkorchid]{Whatever They Join On}[/COLOR][/COLOR][/SIZE][/B]

Remember that for each new item in the SELECT section that is not aggregated, you will need another item in the GROUP BY section.