Dmax or Dlast or Dfirst

[instereo911 via AccessMonster.com]

Good morning,

I have a table set up with the following data

Date Unit RouteCode Receipts
01/01/08 AA 1A 123
01/01/08 AA 2B 456
01/01/08 BB 3C 789
01/06/08 ZZ 2E 2
02/16/08 AA 1A 321
02/16/08 AA 2B 654
02/16/08 BB 3C 987

I am trying to use the DMAX function on an append query to give me results
for the most current date. My data usually runs a day behind so i tried using
date()-1 but then i came accross problems for holidays and weekends (if the
data runs on weekends or holidays). SO i just want to run the most current
date.

Ideally - I would love for it to append the query with whatever dates are
missing from that table. So if the table was missing 01/06 and 02/16/08 it
would know to append those fields onto the table. But if that doesn't work, I
would like it to just append the most current date of 02/16/08.

Make sense? probably not.

Thank you again everyone

 

[Jerry Whittle]

Why settle for a Silver or Bronze. Let's go for the Gold!

INSERT INTO Instereo911 ( [Date], RouteCode, Unit, ID, Receipts )
SELECT Instereo911_Update.[Date],
Instereo911_Update.RouteCode,
Instereo911_Update.Unit,
Instereo911_Update.ID,
Instereo911_Update.Receipts
FROM Instereo911_Update LEFT JOIN Instereo911
ON Instereo911_Update.[Date] = Instereo911.[Date]
WHERE (((Instereo911.Date) Is Null));

With the proper table names, this query should insert records from one table
to the other if there isn't a matching date.

 

[instereo911 via AccessMonster.com]

Worked Perfect Jerry... Thank you so much..


Last question though- i do need (for another query) to show only the last
date. For the same example below... how would i use Date max to only show on
one query ... So for example... I want to show all of the data for 02/16/08
(because that is the last date on my table) but i want to show everything on
02/16/08 (AA,AA,BB and everything else next to it)

How do i do that... Dmax is not working for me.

Why settle for a Silver or Bronze. Let's go for the Gold!

INSERT INTO Instereo911 ( [Date], RouteCode, Unit, ID, Receipts )
SELECT Instereo911_Update.[Date],
Instereo911_Update.RouteCode,
Instereo911_Update.Unit,
Instereo911_Update.ID,
Instereo911_Update.Receipts
FROM Instereo911_Update LEFT JOIN Instereo911
ON Instereo911_Update.[Date] = Instereo911.[Date]
WHERE (((Instereo911.Date) Is Null));

With the proper table names, this query should insert records from one table
to the other if there isn't a matching date.

Good morning,

[quoted text clipped - 23 lines]

Thank you again everyone

 

[Jerry Whittle]

Create a query with all the fields. Sort by the date field in descending
order so that the latest dates are on top. Then open up the query in SQL
View. Change the word

SELECT

to

SELECT TOP 1
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Worked Perfect Jerry... Thank you so much..


Last question though- i do need (for another query) to show only the last
date. For the same example below... how would i use Date max to only show on
one query ... So for example... I want to show all of the data for 02/16/08
(because that is the last date on my table) but i want to show everything on
02/16/08 (AA,AA,BB and everything else next to it)

How do i do that... Dmax is not working for me.

Why settle for a Silver or Bronze. Let's go for the Gold!

INSERT INTO Instereo911 ( [Date], RouteCode, Unit, ID, Receipts )
SELECT Instereo911_Update.[Date],
Instereo911_Update.RouteCode,
Instereo911_Update.Unit,
Instereo911_Update.ID,
Instereo911_Update.Receipts
FROM Instereo911_Update LEFT JOIN Instereo911
ON Instereo911_Update.[Date] = Instereo911.[Date]
WHERE (((Instereo911.Date) Is Null));

With the proper table names, this query should insert records from one table
to the other if there isn't a matching date.

Good morning,

[quoted text clipped - 23 lines]

Thank you again everyone

 

[instereo911 via AccessMonster.com]

Wow that easy!!!

Thank you very much Jerry!!!

Create a query with all the fields. Sort by the date field in descending
order so that the latest dates are on top. Then open up the query in SQL
View. Change the word

SELECT

to

SELECT TOP 1

Worked Perfect Jerry... Thank you so much..

[quoted text clipped - 25 lines]