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;

 

[Joe4]

You would have to have some sort of criteria to tell it which records to filter out.
Select queries do not update any fields. So I don't think you can do this quite the way you envision.
You would need to update something ("fieldfs") somewhere, so your criteria would have something to filter on.

One possible way would be to write the results of your last query to a temp table.
Then, do an unmatched query betweeen the new query and the temp table, to identify all new records.
Then you would need to wipe the temp table clean, and populate it to prepare for the next comparison.
And you would need to do this every time.

It might be easier to simply have a "last updated" field in each table, and make sure it gets updated anything any data is changed on a record.

 

[jarett]

I've read some articles on timestamps, would it be possible to add a field in the last query to insert a timestamp when a record is created in it? Then put some kind of formula in the criteria?

 

[jarett]

I did find a DateUpdate and TimeUpdate field in one of the tables, could I use those?

 

[Joe4]

I've read some articles on timestamps, would it be possible to add a field in the last query to insert a timestamp when a record is created in it? Then put some kind of formula in the criteria?

You cannot add "fields" to queries. All the queries are a set of "rules" and "filters". The data does not reside in the query, it resides in the underlying tables. It is just displayed through the query. The query object itself has no table record data of its own. You would need to update fields on the underlying tables, and that would have to be done through an Update Query, not a Select Query.

I did find a DateUpdate and TimeUpdate field in one of the tables, could I use those?

Those will tell you when that data record was last updated (when the data last changed).
It will not tell you when the data was last included in a query.
However, if you have a process, like you are going to run the query at the same time every day, then you may be able to use that logic, and just look to return records whose date/time stamps have changed since that scheduled time you are running the query every day.

 

[Micron]

You cannot add "fields" to queries.

Beg to differ. A query calculated field can certainly added, and that field can be included in a report, which means the calculated field and the rest of the data is contained in the query. A query is just one example of a domain. The usual perception of a domain is that it is a table, but you can use domain functions against a query. If the data was not returned to the query, you couldn't use it as a domain. At least that's my take.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: New Records in Query
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Joe4]

Beg to differ. A query calculated field can certainly added, and that field can be included in a report, which means the calculated field and the rest of the data is contained in the query. A query is just one example of a domain. The usual perception of a domain is that it is a table, but you can use domain functions against a query. If the data was not returned to the query, you couldn't use it as a domain. At least that's my take.

I should have been clearer. When I mean is that the calculated value is not added as some "stored" value in the query (i.e. you can cannot calculate some date/time, freeze it in time, and store that hard-coded calculated value at the query level). Any value that you want to to calculate and store that particular calculated value needs to be stored at the table level.

Any calculated field at the query level is dynamic and calculated at run-time.
But I think you already knew that!

 

[jarett]

I might not be using the correct terms, but I was thinking of the way I added these two columns in my query. I do not these these values saved in the db, but I do need these 2 columns in my export to the csv file. I was thinking of trying to filter either a new column/field or the existing date/time fields, either way I don't need anything saved in tables .

 

[Joe4]

I might not be using the correct terms, but I was thinking of the way I added these two columns in my query. I do not these these values saved in the db, but I do need these 2 columns in my export to the csv file. I was thinking of trying to filter either a new column/field or the existing date/time fields, either way I don't need anything saved in tables .

I am not quite sure I understand what you are trying to tell us here.
Yes, you can include Calculated Fields in your query and export them to your CSV file.
Those calculations will exist in the CSV file, but will not be saved in any underlying table field.
And if you run the same query again at a later date, it will recalculate those values at run-time.
If the data in the fields used in the Calculated Fields has not changed, it should return the same value. But if the underlying data has changed, the Calculated Fields in the query will change to reflect the data changes (queries are always "real-time", and reflect the most recent data from your underlying tables).

Does that answer your question?
If not, please explain in more detail. Maybe show us some actual data examples along with your expected output.