Compare a field in access for any weekly changes

[KevinSlater]

Hi,

I have a query in Microsoft Access 2003 named: "QRYPRODUCT_MANUFACTURING_SITES", this query looks at another query which in turns looks at 3 oracle tables.

What I would like to do is do a weekly comparison of the manufacturing site for all products from the most recent purchase order(s) that are marked as complete (ie where the field POP_COMPLETED_FLAG = Y) to the next most recent purchase order(s) marked as complete (ie where the field POP_COMPLETED_FLAG = Y) to see if the manufacturing site for any products has been changed.


The query contains a fieled named: POP_DATE_ITEM_RECEIVED which is the date the purchase order is complete and the day the POP_COMPLETED_FLAG will be set to Y.

So for example today in purchase order number P00164040 a product part code named: 2356NXA is manufactured at site1. But next week a new Purchase order is created with a new order number of: P00141651 with the same product part code: 2356NXA but the manufactured site has now been changed to: Site2. I want to show this change in a query if possible?


If anyone has any suggestions on how to do this that would be great.

 

[namliam]

Yes possible (ofcourse)

Simply join back the products manufactured last week to the products that were created the week before and find any changes....

 

[KevinSlater]

Please can you clarify what needs to be done?, do you mean have 2 querys.

first query: looks at the last 7 days
second query: looks at the last 14 days

and then do a new query of a comparison of the two queries?.

 

[namliam]

Exactemundo!

 

[KevinSlater]

hm ok, ive created the first two querys by having >=DateAdd("d",-7,Date())
and >=DateAdd("d",-14,Date()) in the POP_DATE_ITEM_RECEIVED field

but im not sure how to comare the COM_DATA_VALUE filed (this filed contains the manufactured site). Ive tried doing an unmatched query but this doesnt work and it just put a null criteria in the query.

can you help on this please?

 

[namliam]

The 14 day one should also be bigger than 7 days otherwize you get them too... you dont want them...
>=DateAdd("d",-14,Date()) and <DateAdd("d",-7,Date())

Then do a query where you join the products, simply add
Query1.COM_DATA_VALUE <> Query2.COM_DATA_VALUE

 

[KevinSlater]

Ok the query seems to work ok now thanks that compares the other 2 queries, the only issue is when i try to create a report based on the query it says: "The specified field '[PURCHASE ORDER]' could refer to more than one table listed in the FROM clause of your SQL."

this issue happens for any field i put in the report

The sql is: SELECT DISTINCT QRYPRODUCT_MANUFACTURING_SITES_WEEK.POP_ITEM_NO, QRYPRODUCT_MANUFACTURING_SITES.COM_DATA_VALUE, QRYPRODUCT_MANUFACTURING_SITES_WEEK.COM_DATA_VALUE, QRYPRODUCT_MANUFACTURING_SITES.[PURCHASE ORDER], QRYPRODUCT_MANUFACTURING_SITES_WEEK.[PURCHASE ORDER], QRYPRODUCT_MANUFACTURING_SITES_WEEK.STK_PART_CODE, QRYPRODUCT_MANUFACTURING_SITES_WEEK.POP_COMPLETED_FLAG, QRYPRODUCT_MANUFACTURING_SITES_WEEK.POP_DATE_DUE
FROM QRYPRODUCT_MANUFACTURING_SITES_WEEK, QRYPRODUCT_MANUFACTURING_SITES
WHERE (((QRYPRODUCT_MANUFACTURING_SITES_WEEK.COM_DATA_VALUE)<>QRYPRODUCT_MANUFACTURING_SITES.COM_DATA_VALUE));

Ive tried defining exactly which field to look in the exact query such as QRYPRODUCT_MANUFACTURING_SITES_WEEK.[PURCHASE ORDER]

but this doesnt work, any ideas?

 

[namliam]

If your query works, then the problem is someplace in the report....

The sql you posted looks sound too... Except your not joining the two queries??
Dont you need to know the differences per Proeuct or something?? You probably need to join the queries, but that should not impact this message....

 

[KevinSlater]

ok ive joined to queries by product code, and i've found that the problem in the report was a field was listed in the sorting and grouping but was looking at the wrong field report works fine now.

but I've found one potential issue ...if multiple orders are created in the last 14 days and the same product code is used for those orders but the manufacturing site is say site1 for three of the orders but site2 for five of the orders, then i guess the comparison query/report would show all of those changes which is not ideal.

Is it possible to just compare any manufacturing site changes made to any product in the last 7 days against the very most recent purchase order where that product is listed the week before that?.

 

[namliam]

I would guess... yes....

But what happens if an item is produced on site 1 and 2 during week1
Then site 2 on week2
and site 1 on week3
??

Doesnt seem to be a change, yet it will be listed...

 

[KevinSlater]

Hm sounds tricky, do you know if it poosible to look at the most recent occurance of a product appearing in the most recent purchase order during week1 (or the last 7 days) with the most recent occurance of a product appearing in a the most recent purchase order during week2.

I could then run the query once every week to do the check.

 

[namliam]

Perhaps you should look for a different corner to find your solution...
I.e. Not look back a week, but store/fetch ALL Product/Production Sites and compare that against the last week. See if any news has come up or not...

 

[KevinSlater]

do you mean instead of one of the querys having this criteria: >=DateAdd("d",-14,Date()) and <DateAdd("d",-7,Date()) i remove this so that its looking at all purchase orders and products?

 

[namliam]

Yes... and perhaps you need to rethink the entire method....
Because if you have 2 production sites... One will always be a change... one will always be "different"

Instead i think you want a "unmatched" query... Listing all production sites that have not previously produced this item... .or something alike...