Query to pull recent record older than today

[Rebel7]

Hi Guys,

Basically I have a query that is suppose to do this:
It is to pull up four columns from the database that are Date, SIN, Phone Number, Address. The database can have many different dates, phone numbers, addresses for one SIN so the database will have the same SIN in different rows. What I want to do is pull the most recent SIN record that is older than today and that has an empty value in Phone Number or "416-555-5555."

What I have so far is

SELECT DISTINCT Table1.Date, Table1.SIN, Table1.[Phone Number], Table1.Address
FROM Table1
WHERE (((Table1.Date)<Date()) AND ((Table1.[Phone Number]) Is Null)) OR (((Table1.[Phone Number])="555-555-5555"));

The problem with this is that I still get duplicate SINs since it is looking for all SINs older than today that have had a record entry when I only want the most recent SIN record older than today and not all the past records for the same SIN.

 

[Brianwarnock]

Try
SELECT Max(Table1.Date) as maxofdate, Table1.SIN, Table1.[Phone Number], Table1.Address
FROM Table1
WHERE (((Table1.Date)<Date()) AND (((Table1.[Phone Number]) Is Null) OR ((Table1.[Phone Number])="416-555-5555"))
Groupby SIN,[phone number],address;


this is air code so syntax errors may exist

Brian

 

[Rebel7]

Hi Brian,

I tried that and got an error saying "You tried to execute a query that does not include the specified SIN as part of an aggregate function."
I played around with it but still got the error. I don't know what to do.

Thanks,

 

[Brianwarnock]

The Group By Sin makes it part of an aggregate , however I did say Groupby, surprised it didn't give a syntax error.

Brian

 

[Rebel7]

Hi Brian,

Thanks that removed the error but I am still getting duplicate SIN records showing. The query is meant to look at the most recent record for each SIN older than today that fall under the other guidelines (ie. phone number = blank or "555-555-5555", etc.) and to ignore the older dated records for the same SIN.

Thanks

 

[Brianwarnock]

Its almost certainly because you are grouping on 3 fields remove the grouping from phone number and address, note that in the design grid you will have to change the totals row entry for phone number to where, the address must be removed from the select.

to get this extra info you will need another query containing this one linked back to the table on date and sin fields.

Brian

 

[Rebel7]

Hi Brian,

Thank you so much for your help. I am almost there. What I have so far is:

SELECT MAX(Table1.Date) AS [Last Date], Table1.SIN
FROM Table1
WHERE (((Table1.Date)<Date()) AND ((Table1.[Phone Number]) Is Null)) OR (((Table1.[Phone Number])="555-555-5555"))
GROUP BY Table1.SIN;

This works out in that it gives me the most recent date older than today for each unique SIN and does not give me duplicate SINs. However, I haven't done multiple queries that work off each other before so I don't know how to display the most recent Address and Phone Number alongside each unique IMEI. Since the IMEI pulled is the most recently actioned, I would like to have the Address and Phone Number for that recently actioned IMEI displayed in the listbox as well.

Thanks again.

 

[Brianwarnock]

It is simple. as is everything when you know how ,
Queries can be joined to each other and tables just like tables so

Select query1.Sin etc selecting from the query or table as appropriate
From Query1 innerjoin table1 on query1.[LAST DATE] =Table1.[Date] and Query1.Sin=Table1.Sin
Order by etc whatever

If you prefer to do it in the design grid pop the query and the table in there and drag the field from one to the other to make the join.

Note You should not use Date as a field name as it is areserved word m a function in fact, and its use will cause you problems, also try to avoid spaces in names as they complicate syntax Last_Date is better, you don't have to remember the [] in SQL and in VBA intellisense can pick up field names.

Brian

 

[Rebel7]

Hi Brian,

What I noticed with my last post query is that now when I action the SINs on the form, the date column of my table is no longer automatically populating with the current date as it did before I put this new query in.
The query was:
SELECT MAX(Table1.Date) AS [Last Date], Table1.SIN
FROM Table1
WHERE (((Table1.Date)<Date()) AND ((Table1.[Phone Number]) Is Null)) OR (((Table1.[Phone Number])="555-555-5555"))
GROUP BY Table1.SIN;

Also, to join this query to another query, I got an error saying Syntax error in FROM clause.

The Query is:
Select Query1.SIN, Table1.[Address], Table1.[Phone Number]
FROM Query1.SIN INNERJOIN Table1 ON Query1.[Date Last Actioned]=Table1.[Date Actioned] AND Query1.SIN=Table1.SIN

Your help is much appreciated.

 

[Brianwarnock]

The From should just day

From query1 innerjoin Table1 on etc
NOT From query1.SIN

I don't understand how aquery against a table can effect the updating of that table via a form. Have you changed anything, names etc in the table/form?

If you cannot fins the problem and are not on 2007 then compact , zip if necessary and attach the db, I'll try and have a look at it.

Brian

 

[Rebel7]

Hi Brian,

Thank you for your help. I figure it out and now that part is working. However, my main concern is that because the Date column in the database has the time included, the sql query looks at the time when considering <Date() because I am pulling results older than now but that is still in the same day because of the time stamp. I would simply change the format of the Date column to exclude the time portion but the time stamp is something we need to see for the date so we cannot remove it.
Any suggestions on this?

Thanks again for all your help.

 

[Brianwarnock]

I don't understand your date problem Date() defaults to a time of 00:00:00 therefore you should not pull in anything for todays date. If you used Now() you would have the problem, but your SQL had Date()

Brian