I have a question, somewhat complex to me as I am new to SQL and to Access~ I'll try to state the question using a simple example (this is somewhat verbose i know)
I have 5 tables each containing about 12k records, each tables records have about 40 columns or fields of data.
These tabls are updated two ways.
One way is via update, delete and append queries, the source of the input that updates the tables is another database used in another application. this all works fine.
The second way is via Forms in an Access application which I have engineered(with help from the wonderful folks here on the forum); the Forms update only new fields in the 5 tables records which I have added and which do not exist in the other applications database.
I have an additional table which contains a snapshot of all the records from the 5 tables; i.e. it contains every record from the 5 tables all consolidated into one enormous table. I am able to "refresh" this table via queries which access the 5 seperate tables (update, delete, append)..... this is done only periodically, like every few days.
What I want to be able to do now is when I run the queries that refresh or update the big consolidated table (from the 5 smaller tables) is to identify, by record, what changed on the consolidated table as compared to the 5 tables....... i.e. lets say a user changed a retail price in a record in table # 4 and an item description in a record in table # 2~
I had hoped to be able to add a series of fields to the records in the consolidated table that would "flag" the fields that were changed, i.e. one flag field on each record that was set to true if anything at all on the record was changed and Yes/No fields that the Update Query could flick to "yes" if a corresponding field in the record was changed,
for example, if the user modified a retail or wholesale price in table #4 the Update Query that takes the records in table #4 and updates the consolidated table could set a new field in the consolidated table records called "pricechg" to "yes" if it updated either the retail price or the Wholesale price in the record during the update.
ConsolidatedTable fields as follows:
Change? PriceChg? DescriptionChg? etc etc ~ data~~>
True/False Yes/No Yes/No
I tried this with the price only and it worked........in the update query, I put in the new field that i added to the consolidated table "PriceChg", in the Update To: I put Yes and in the criteria I put in table#4![whslprice]<>consoltable![whslprice]Or table#4![Rtlprice]<>consoltable![Rtlprice].
When i made a change to either the wholesale or retail price in table #4 and ran the update for the consolidated table it would update the price(s) and also set the flag to Yes ~ (checkmark) ~ this is what i wanted to happen.
Problem is it will only work for the price change~ i looked at the SQl and there is an AND operator in it which appears to state that the updates will not work at all unless the prices are different; i.e. the query must find one of the fields in the record different AND a price difference as well ~
Can someone help me find a way to accomplish what i am trying to do ? (actually the end result is a report that the users will have that they can run periodically that will show what was changed in the 5 tables since the last time they ran the report) ~ I really want to accomplish this by comparing the data in the 5 tables against the "snapshot" table that contains all the records from the 5 tables ~ then running a report off the consolidated table records that shows what data fields were changed ~ (i don't need to show the users what the "old" data looks like, just show them the updated data record and something that tells them what fields were changed (prices, descriptions etc) (i thought that after the report ran, i could clear all the flags in the consolidated table and start the process over next time the user wanted to see the updates that were made~ )
this is the sql ~ :
UPDATE StoreMisc INNER JOIN AllItemsConsolidatedTable ON StoreMisc.UPCode = AllItemsConsolidatedTable.UPCode SET AllItemsConsolidatedTable.ItemLibnum = [StoreMisc]![ItemLibnum], AllItemsConsolidatedTable.ItemName = [StoreMisc]![Item Description], AllItemsConsolidatedTable.BriefDescription = [StoreMisc]![Size], AllItemsConsolidatedTable.CommonName = [StoreMisc]![Short Name], AllItemsConsolidatedTable.UnitPrice = [StoreMisc]![Rtl Price], AllItemsConsolidatedTable.[Whsl Price] = [StoreMisc]![Whsl Price], AllItemsConsolidatedTable.Source = [StoreMisc]![Source], AllItemsConsolidatedTable.Dept = [StoreMisc]![Dept], AllItemsConsolidatedTable.Class = [StoreMisc]![Class], AllItemsConsolidatedTable.SubCls = [StoreMisc]![SubCls], AllItemsConsolidatedTable.Type = [StoreMisc]![Type], AllItemsConsolidatedTable.Comments = [StoreMisc]![Comments], AllItemsConsolidatedTable.[Sign line 1] = [StoreMisc]![Sign Line1], AllItemsConsolidatedTable.[Sign line 2] = [StoreMisc]![Sign Line2], AllItemsConsolidatedTable.[Sign line 3] = [StoreMisc]![Sign Line3], AllItemsConsolidatedTable.[Sign line 4] = [StoreMisc]![Sign Line4], AllItemsConsolidatedTable.[Sign line 5] = [StoreMisc]![Sign Line5], AllItemsConsolidatedTable.[Tag line 1] = [StoreMisc]![Sign Line1], AllItemsConsolidatedTable.[Tag line 2] = [StoreMisc]![Sign Line2], AllItemsConsolidatedTable.[Tag line 3] = [StoreMisc]![Sign Line3], AllItemsConsolidatedTable.[Tag line 4] = [StoreMisc]![Sign Line4], AllItemsConsolidatedTable.[Tag line 5] = [StoreMisc]![Sign Line5], AllItemsConsolidatedTable.UpDateFlag = "True", AllItemsConsolidatedTable.PriceChg = Yes
WHERE (([AllItemsConsolidatedTable]![UnitPrice]<>[StoreMisc]![Rtl Price] Or [StoreMisc]![Whsl Price]<>[AllItemsConsolidatedTable]![Whsl Price] Or [AllItemsConsolidatedTable]![ItemName]<>[StoreMisc]![Item Description] Or [StoreMisc]![Dept]<>[AllItemsConsolidatedTable]![Dept] Or [AllItemsConsolidatedTable]![Class]<>[StoreMisc]![Class] Or [StoreMisc]![SubCls]<>[AllItemsConsolidatedTable]![SubCls] Or [AllItemsConsolidatedTable]![Type]<>[StoreMisc]![Type] Or [StoreMisc]![Short Name]<>[AllItemsConsolidatedTable]![CommonName] Or [AllItemsConsolidatedTable]![Sign line 1]<>[StoreMisc]![Sign Line1] Or [StoreMisc]![Sign Line2]<>[AllItemsConsolidatedTable]![Sign line 2] Or [AllItemsConsolidatedTable]![Sign line 3]<>[StoreMisc]![Sign Line3] Or [StoreMisc]![Sign Line4]<>[AllItemsConsolidatedTable]![Sign line 4] Or [AllItemsConsolidatedTable]![Sign line 5]<>[StoreMisc]![Sign Line5]) AND ([AllItemsConsolidatedTable]![UnitPrice]<>[StoreMisc]![Rtl Price] Or [StoreMisc]![Whsl Price]<>[AllItemsConsolidatedTable]![Whsl Price]));
thanks
Paul [sig][/sig]
I have 5 tables each containing about 12k records, each tables records have about 40 columns or fields of data.
These tabls are updated two ways.
One way is via update, delete and append queries, the source of the input that updates the tables is another database used in another application. this all works fine.
The second way is via Forms in an Access application which I have engineered(with help from the wonderful folks here on the forum); the Forms update only new fields in the 5 tables records which I have added and which do not exist in the other applications database.
I have an additional table which contains a snapshot of all the records from the 5 tables; i.e. it contains every record from the 5 tables all consolidated into one enormous table. I am able to "refresh" this table via queries which access the 5 seperate tables (update, delete, append)..... this is done only periodically, like every few days.
What I want to be able to do now is when I run the queries that refresh or update the big consolidated table (from the 5 smaller tables) is to identify, by record, what changed on the consolidated table as compared to the 5 tables....... i.e. lets say a user changed a retail price in a record in table # 4 and an item description in a record in table # 2~
I had hoped to be able to add a series of fields to the records in the consolidated table that would "flag" the fields that were changed, i.e. one flag field on each record that was set to true if anything at all on the record was changed and Yes/No fields that the Update Query could flick to "yes" if a corresponding field in the record was changed,
for example, if the user modified a retail or wholesale price in table #4 the Update Query that takes the records in table #4 and updates the consolidated table could set a new field in the consolidated table records called "pricechg" to "yes" if it updated either the retail price or the Wholesale price in the record during the update.
ConsolidatedTable fields as follows:
Change? PriceChg? DescriptionChg? etc etc ~ data~~>
True/False Yes/No Yes/No
I tried this with the price only and it worked........in the update query, I put in the new field that i added to the consolidated table "PriceChg", in the Update To: I put Yes and in the criteria I put in table#4![whslprice]<>consoltable![whslprice]Or table#4![Rtlprice]<>consoltable![Rtlprice].
When i made a change to either the wholesale or retail price in table #4 and ran the update for the consolidated table it would update the price(s) and also set the flag to Yes ~ (checkmark) ~ this is what i wanted to happen.
Problem is it will only work for the price change~ i looked at the SQl and there is an AND operator in it which appears to state that the updates will not work at all unless the prices are different; i.e. the query must find one of the fields in the record different AND a price difference as well ~
Can someone help me find a way to accomplish what i am trying to do ? (actually the end result is a report that the users will have that they can run periodically that will show what was changed in the 5 tables since the last time they ran the report) ~ I really want to accomplish this by comparing the data in the 5 tables against the "snapshot" table that contains all the records from the 5 tables ~ then running a report off the consolidated table records that shows what data fields were changed ~ (i don't need to show the users what the "old" data looks like, just show them the updated data record and something that tells them what fields were changed (prices, descriptions etc) (i thought that after the report ran, i could clear all the flags in the consolidated table and start the process over next time the user wanted to see the updates that were made~ )
this is the sql ~ :
UPDATE StoreMisc INNER JOIN AllItemsConsolidatedTable ON StoreMisc.UPCode = AllItemsConsolidatedTable.UPCode SET AllItemsConsolidatedTable.ItemLibnum = [StoreMisc]![ItemLibnum], AllItemsConsolidatedTable.ItemName = [StoreMisc]![Item Description], AllItemsConsolidatedTable.BriefDescription = [StoreMisc]![Size], AllItemsConsolidatedTable.CommonName = [StoreMisc]![Short Name], AllItemsConsolidatedTable.UnitPrice = [StoreMisc]![Rtl Price], AllItemsConsolidatedTable.[Whsl Price] = [StoreMisc]![Whsl Price], AllItemsConsolidatedTable.Source = [StoreMisc]![Source], AllItemsConsolidatedTable.Dept = [StoreMisc]![Dept], AllItemsConsolidatedTable.Class = [StoreMisc]![Class], AllItemsConsolidatedTable.SubCls = [StoreMisc]![SubCls], AllItemsConsolidatedTable.Type = [StoreMisc]![Type], AllItemsConsolidatedTable.Comments = [StoreMisc]![Comments], AllItemsConsolidatedTable.[Sign line 1] = [StoreMisc]![Sign Line1], AllItemsConsolidatedTable.[Sign line 2] = [StoreMisc]![Sign Line2], AllItemsConsolidatedTable.[Sign line 3] = [StoreMisc]![Sign Line3], AllItemsConsolidatedTable.[Sign line 4] = [StoreMisc]![Sign Line4], AllItemsConsolidatedTable.[Sign line 5] = [StoreMisc]![Sign Line5], AllItemsConsolidatedTable.[Tag line 1] = [StoreMisc]![Sign Line1], AllItemsConsolidatedTable.[Tag line 2] = [StoreMisc]![Sign Line2], AllItemsConsolidatedTable.[Tag line 3] = [StoreMisc]![Sign Line3], AllItemsConsolidatedTable.[Tag line 4] = [StoreMisc]![Sign Line4], AllItemsConsolidatedTable.[Tag line 5] = [StoreMisc]![Sign Line5], AllItemsConsolidatedTable.UpDateFlag = "True", AllItemsConsolidatedTable.PriceChg = Yes
WHERE (([AllItemsConsolidatedTable]![UnitPrice]<>[StoreMisc]![Rtl Price] Or [StoreMisc]![Whsl Price]<>[AllItemsConsolidatedTable]![Whsl Price] Or [AllItemsConsolidatedTable]![ItemName]<>[StoreMisc]![Item Description] Or [StoreMisc]![Dept]<>[AllItemsConsolidatedTable]![Dept] Or [AllItemsConsolidatedTable]![Class]<>[StoreMisc]![Class] Or [StoreMisc]![SubCls]<>[AllItemsConsolidatedTable]![SubCls] Or [AllItemsConsolidatedTable]![Type]<>[StoreMisc]![Type] Or [StoreMisc]![Short Name]<>[AllItemsConsolidatedTable]![CommonName] Or [AllItemsConsolidatedTable]![Sign line 1]<>[StoreMisc]![Sign Line1] Or [StoreMisc]![Sign Line2]<>[AllItemsConsolidatedTable]![Sign line 2] Or [AllItemsConsolidatedTable]![Sign line 3]<>[StoreMisc]![Sign Line3] Or [StoreMisc]![Sign Line4]<>[AllItemsConsolidatedTable]![Sign line 4] Or [AllItemsConsolidatedTable]![Sign line 5]<>[StoreMisc]![Sign Line5]) AND ([AllItemsConsolidatedTable]![UnitPrice]<>[StoreMisc]![Rtl Price] Or [StoreMisc]![Whsl Price]<>[AllItemsConsolidatedTable]![Whsl Price]));
thanks
Paul [sig][/sig]