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;
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

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

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

Board Regular
Joined
Apr 12, 2021
Messages
73
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I did find a DateUpdate and TimeUpdate field in one of the tables, could I use those?
 

Joe4

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

ADVERTISEMENT

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

Well-known Member
Joined
Jun 3, 2015
Messages
2,094
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

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

query fields.png
 

Joe4

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

Watch MrExcel Video

Forum statistics

Threads
1,130,323
Messages
5,641,530
Members
417,215
Latest member
Diaryman

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