New Records in Query

jarett

Board Regular
Joined
Apr 12, 2021
Messages
66
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
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

Board Regular
Joined
Apr 12, 2021
Messages
66
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 12, 2021
Messages
66
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
Rich (BB code):
Cdbl([TimeUpdated])>13
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

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

Add this criterion to your current query:

SQL:
[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

Board Regular
Joined
Apr 12, 2021
Messages
66
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

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

Add this criterion to your current query:

SQL:
[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;
SQL:
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;
lastrun.png
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
66
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

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

jarett

Board Regular
Joined
Apr 12, 2021
Messages
66
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
design.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,890
Messages
5,638,857
Members
417,055
Latest member
SerenaCruz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top