SQL Query and Form Control Source Issue

[dhogan444]

I have three tables



tblCustomers -lists customer info
Fields: ID, Name

tblIncrements - defines increments
Fields: ID, IncrementName, IncrementDetails

tblIncrementHistory - keeps track of which increment the customers is and was assigned to, the customers can only be assigned to one increment, but I have to keep track of their past history. The way I know their current history is it is the most recent assigned increment.
Fields: ID, CustomerID (relationship), IncrementID (relationship), ChangeDate


Now, on my Customer form, I want to query the increment name of the increment they are currently assigned to and list that in a txt box. Here is the query I attempted to write, which seems to work okay when I do it in SQL as a test query and change the forms Customer ID to "2":

SELECT TOP 1 [tblIncrements].[IncrementName]
FROM tblIncrements, tblIncrementHistory
WHERE tblIncrementHistory.CustomerID=2 And tblIncrementHistory.IncrementID=tblIncrements.ID
ORDER BY [tblIncrementHistory].[ChangeDate] DESC;

I am not sure how to pass the form's current Customers.ID variable into the SQL query, and think that is why I am having a problem. However, when I attempted to use this code in the text box with 2, I get #Name? in the text box, so I am really confused.

Your help would be greatly appreciated.

Thank you

[jzwp11]

Actually, you should use an aggregate query to get the most recent increment change date for each customer. Something like this:

query name: qryMostRecentIncrementDate
SELECT tblIncrementHistory.CustomerID, Max(tblIncrementHistory.ChangeDate) AS MaxOfChangeDate
FROM tblIncrementHistory
GROUP BY tblIncrementHistory.CustomerID;

Then create another query that joins the above back to the increment history & increments table

query name: qryCurrentIncrementForEachCustomer

SELECT tblIncrementHistory.CustomerID, tblIncrementHistory.IncrementID, qryMostRecentIncrementDate.MaxOfChangeDate, tblIncrements.IncrementName, tblIncrements.IncrementDetails
FROM (qryMostRecentIncrementDate INNER JOIN tblIncrementHistory ON (qryMostRecentIncrementDate.MaxOfChangeDate = tblIncrementHistory.ChangeDate) AND (qryMostRecentIncrementDate.CustomerID = tblIncrementHistory.CustomerID)) INNER JOIN tblIncrements ON tblIncrementHistory.IncrementID = tblIncrements.IncrementID;


With respect to your form, I would recommend using a DLookup() function as the control source for the text box. The Dlookup() would reference the qryCurrentIncrementForEachCustomer.

I've attached a database to illustrate.

Also, I would recommend using a more descriptive field name for the ID field in the tblCustomers. Also, the word "name" is a reserved word in Access so it should not be used as a table or field name.

[dhogan444]

Thank you for helping me, this was perfect!

[jzwp11]

You're welcome.

[dhogan444]

Okay I am revisiting this thread because I am having some issues. The dlookup is very slow, I have 3000+ records and as I scroll through the records it takes its time to do the lookup. The other problem I have is I am trying to filter based on the dlookup value, which I was able to successfully do by using the filter property in the form, however this is VERY slow and takes a minute or two for it to filter. Any ideas?

[jzwp11]

Yes, the domain aggregate functions can be slow. You could filter out some of the older records from which the aggregate query pulls its info by adding a date criteria. Another option is to base your form on a query that joins the table to the query that the Dlookup was obtaining its data, but that might cause the recordset to become un-updateable which would mean that you would not be able to edit the records in the form. Are you using the form for editing as well as viewing records?