New Records in Query

[jarett]

I am pulling data from 4 tables in a combination of 3 queries. All 3 queries contain one field that is common "PurchaseOrderNo", the final query produces some filter data and only the information needed. I am trying to figure out how to specify the query to only produce new data results since the query was last run, if that makes sense. this is my SQL

SELECT po_detail2.PurchaseOrderNo, po_detail2.VendorNo, po_detail2.ItemCode, po_detail2.LotSerialNo, IM068_MXPUnivProdCode.UDF_UNIQUE_KEY, Right([UDF_UNIQUE_KEY],1) AS SIZE_INDEX, Left([UDF_UNIQUE_KEY],Len([UDF_UNIQUE_KEY])-1) AS INVENTORY_KEY
FROM po_detail2 LEFT JOIN IM068_MXPUnivProdCode ON po_detail2.LotSerialNo = IM068_MXPUnivProdCode.LotSerialNo
WHERE (((po_detail2.PurchaseOrderNo)="0056334" Or (po_detail2.PurchaseOrderNo)>"0056334") AND ((po_detail2.ItemCode)="K500" Or (po_detail2.ItemCode)="PC55"))
ORDER BY po_detail2.PurchaseOrderNo DESC;

 

[jarett]

Let me through this out there. If we only run the query once a day could I add a filter to the query to only include records updated (using DateUpdated & TimeUpdated field)

Now() Or Date(Now()-1) - Date field *Works For pulling the correct fields*
Time field- struggling with this one IIf([DateUpdated]=now(),[TimeUpdated]<12.99999, ?) Or IIf([DateUpdated]=now()-1,[TimeUpdated]>13, ?) this didnt work, but I thought it sounded good.

 

[Joe4]

Is there a reason I keep getting this message when I run the make table query?
View attachment 36889

A "Make Table" query creates a new table. So if you run it more than once using the same Table name, it will overwrite the existing one, which is probably OK.
So you can ignore that error (there is a command to suppress that called "SetWarnings" in Macros and VBA.

The other option would be to create it once, and then delete the data only each time, and then run an Append Query to add the new data to the existing secondary table.
(As you can see, there are different ways of coming at this).

 

[Joe4]

Let me through this out there. If we only run the query once a day could I add a filter to the query to only include records updated (using DateUpdated & TimeUpdated field)

Now() Or Date(Now()-1) - Date field *Works For pulling the correct fields*
Time field- struggling with this one IIf([DateUpdated]=now(),[TimeUpdated]<12.99999, ?) Or IIf([DateUpdated]=now()-1,[TimeUpdated]>13, ?) this didnt work, but I thought it sounded good.

If you go to Table Design on your data table, what is the Data Type and Format of this date field?

 

[jarett]

If you go to Table Design on your data table, what is the Data Type and Format of this date field?

DateUpdated- data type= Date/Time no format set, the format from the source is set at YYYYMMDD, but in access it reads mm/dd/yyyy
TimeUpdated- data type= Short Text, field size 8, no format set

 

[Joe4]

TimeUpdated- data type= Short Text, field size 8, no format set

That is probably why your criteria is not working for this field. It is not a numeric (or date/time) field, but rather a text one.
You need to convert that to number, using a text-to-number conversion function like "Cdbl".

So in your Criteria, you would have something like:

Cdbl([TimeUpdated])>13

 

[JonXL]

Create a table with one field: dtmQueryLastRun as datatype date/time formatted to accept a date with a time. Name the table tblQueryLastRun

Create a query called qrySetQueryLastRun and fill with the following SQL:

INSERT INTO tblQueryLastRun (dtmQueryLastRun) VALUES (Now());

Add this criterion to your current query:

[TimeUpdated] > DMax("dtmQueryLastRun", "tblQueryLastRun")

Create a Macro to run your main query first followed by your second query (the one you just made called qrySetQueryLastRun).

Go to table tblQueryLastRun and add one record and one record only with the last date and time you ran your main query - you have to set that time manually on the first run but after this you should never need to touch that table unless looking to see when your last run was if for some reason you just wanted to know that.

Now run the Macro you made and watch the magic. It pulls everything since the last time you ran the query and then updates the running timestamp history table with when you ran the query so it knows when you run it again which records to pull back...

 

[jarett]

Create a table with one field: dtmQueryLastRun as datatype date/time formatted to accept a date with a time. Name the table tblQueryLastRun

Create a query called qrySetQueryLastRun and fill with the following SQL:

INSERT INTO tblQueryLastRun (dtmQueryLastRun) VALUES (Now());

Add this criterion to your current query:

[TimeUpdated] > DMax("dtmQueryLastRun", "tblQueryLastRun")

Create a Macro to run your main query first followed by your second query (the one you just made called qrySetQueryLastRun).

Go to table tblQueryLastRun and add one record and one record only with the last date and time you ran your main query - you have to set that time manually on the first run but after this you should never need to touch that table unless looking to see when your last run was if for some reason you just wanted to know that.

Now run the Macro you made and watch the magic. It pulls everything since the last time you ran the query and then updates the running timestamp history table with when you ran the query so it knows when you run it again which records to pull back...

I entered everything and it looks like the information is going in and out of the tables but, the query is still including the records from previously run queries. Could it be due to the format of the [TimeUpdated] field mentioned by Joe4? This is the SQL for the main query;

SELECT IM068_MXPUnivProdCode.UDF_UNIQUE_KEY, Right([UDF_UNIQUE_KEY],1) AS SIZE_INDEX, Left([UDF_UNIQUE_KEY],Len([UDF_UNIQUE_KEY])-1) AS INVENTORY_KEY, po_detail2.VendorNo, po_detail2.ItemCode, po_detail2.LotSerialNo, po_detail2.DateUpdated, po_detail2.TimeUpdated, po_detail2.PurchaseOrderNo, po_detail2.[Qty Ordered]
FROM po_detail2 INNER JOIN IM068_MXPUnivProdCode ON po_detail2.LotSerialNo = IM068_MXPUnivProdCode.LotSerialNo
WHERE (((po_detail2.ItemCode)="K500" Or (po_detail2.ItemCode)="PC55") AND ((po_detail2.TimeUpdated)>DMax("dtmQueryLastRun","tblQueryLastRun")))
ORDER BY po_detail2.PurchaseOrderNo DESC;

Tried adding the "cdbl" before the ">DMAX..." criterion, not sure if that was correct. And this is the "lastrun" table records, the first record at 8:30 was the one I entered;

 

[jarett]

Trying to figure out the steps of the "query last run" method, I'm thinking I would have to add a field to the main query, not positive on placing the "> DMax("dtmQueryLastRun", "tblQueryLastRun")" expression in the criteria of the field [TimeUpdated] in my main query. How does it see the "lastrun" values from here?

 

[JonXL]

You need to convert your [TimeUpdated] to a date/time. It sounds like it's currently text. So you might need something like this in your query:

CDate([TimeUpdated]) > DMax("dtmQueryLastRun", "tblQueryLastRun")

 

[jarett]

I've tried to place the statements all over and with different formats, the query runs but never gives any results. I enter data into the DB before I run the query so I know there should be a record in the DB entered after I ran the last query. I am thinking I am missing a link somewhere to tie in that "querylastrun" table? This is my design view of the main query.