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]

So this is the best example I can try to give. The first screen shot would be the first query result of the day.

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

 

[Joe4]

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]

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]

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]

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]

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]

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]

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]

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]

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?