New Records in Query

jarett

Board Regular
Joined
Apr 12, 2021
Messages
73
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
73
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So this is the best example I can try to give. The first screen shot would be the first query result of the day.
queryresult 1.png


The next time I run the query I would only want the highlighted (new*) fields produced or exported to the csv.
queryresult 2.png
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,022
Office Version
  1. 365
Platform
  1. Windows
Based on those screen prints, you should be able to simply filter on the "DateUpdate" field, only pulling back records where the date is equal to the current date.
So, in the Query Builder, you would just put "Date()" on the Criteria line under the "DateUpdate" field.
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
73
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Based on those screen prints, you should be able to simply filter on the "DateUpdate" field, only pulling back records where the date is equal to the current date.
So, in the Query Builder, you would just put "Date()" on the Criteria line under the "DateUpdate" field.
But what if on the next query the next results have the same date, I didn't show that in the screenshots. If I schedule the query's to run say 10:00am, 12:00pm, and 2:00pm could I use the Date() and some sort of filter in the time field to look for anything -2 hours, I guess that wouldn't work when you run the first query the next day though?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,092
Office Version
  1. 365
Platform
  1. Windows
Your table field needs a CalcDate (calculated date) field that you set the date on when you create the csv file, or some way of storing a similar flag in that table. If you cannot because it is read only for example, then one way is to duplicate this table, populate it with the data and write date/time to the date/time field that you have in this new table. You'd leave these records there for as long as practical and possible. The only other way I can think of at the moment is to have a date/time field in a new table but that will only say when you last transferred data. It wont' be on a record by record basis like the other method will.
 

Joe4

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

ADVERTISEMENT

Are old records ever updated, or just new records added?
If old record are updated, do they need to be included on the filter?
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
73
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Your table field needs a CalcDate (calculated date) field that you set the date on when you create the csv file, or some way of storing a similar flag in that table. If you cannot because it is read only for example, then one way is to duplicate this table, populate it with the data and write date/time to the date/time field that you have in this new table. You'd leave these records there for as long as practical and possible. The only other way I can think of at the moment is to have a date/time field in a new table but that will only say when you last transferred data. It wont' be on a record by record basis like the other method will.
Which table are you referring to? The csv is created from the query, so technically it is not a table, or are you talking about the new table I would have to create?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,092
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Your query is getting data from 4 tables, so I meant one of those when suggesting the first case. However I realize that the table you might use for this might be read only or you can't modify it even if you wanted to. So the second table reference is about one that you create and populate from your 4 table query (a simple make table query can use this query to create that table). Then you add the date field to this new table and when you create the csv file from this new table, you date or date/time stamp that field for each of those records. How you get new records into this new table isn't clear to me at the moment because I'd have to go back to page one here, which I can't do while composing this. Your current 4 table query might only contain new data, which means you'd simply append its results to your new table. If it is a mix of old and new, then you can set up the new table to not allow dupes on a certain field. When you attempt to append all the data from the 4 table query, the old records will be rejected.

Joe4 raises a good point - I'm thinking along the lines of a one-time operation which means the records that will be transposed won't be included next time regardless. If you want all of those records to be appended so that they transpose to the csv file again, you'd simply append them regardless. Because they won't have a date stamp, they will transpose to the csv file.

If that's not clear let us/me know, but take it as an overview of how it might be done rather than a blueprint for how to build it.
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
73
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Are old records ever updated, or just new records added?
If old record are updated, do they need to be included on the filter?
In theory yes old records could be updated (rare), but we wouldn't want them to be included on the filter.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,022
Office Version
  1. 365
Platform
  1. Windows
In theory yes old records could be updated (rare), but we wouldn't want them to be included on the filter.
So, if you only want to see the new records, and don't care about the old records (even if they are updated), one simple way would be to use an Autonumber field. Simply add it to your table, and whenever new records are added, the system will automatically assign a random, UNIQUE identifier to each record.

Then each time you run a query, you could either:
- Write a copy of the data to another table in Access
- Export the data with the Autonumber to a CSV or other file

Then, you can simply do an unmatched query between your main data table to the previously exported data (or other table) on the Autonumber field, and pick out just the records.

Just some ideas to consider...
 

jarett

Board Regular
Joined
Apr 12, 2021
Messages
73
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So, if you only want to see the new records, and don't care about the old records (even if they are updated), one simple way would be to use an Autonumber field. Simply add it to your table, and whenever new records are added, the system will automatically assign a random, UNIQUE identifier to each record.

Then each time you run a query, you could either:
- Write a copy of the data to another table in Access
- Export the data with the Autonumber to a CSV or other file

Then, you can simply do an unmatched query between your main data table to the previously exported data (or other table) on the Autonumber field, and pick out just the records.

Just some ideas to consider...
Is there a reason I keep getting this message when I run the make table query?
1618605426920.png
 

Watch MrExcel Video

Forum statistics

Threads
1,130,154
Messages
5,640,445
Members
417,143
Latest member
boukadidanizar

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