In Access 2010, I have two tables - one with purchases and one with sales.
My ultimate goal is to create a report that will show the oldest purchase date for each of my products. In other words, I'm trying to determine which ones are sitting around collecting dust. As an example, assume I had purchased three cokes to resell and they were purchased like this - one in January, one in February, and one in March. When I look at my inventory, I want to see beside "Coke" that date in January because that is the oldest "Coke" I still have. If I sell one, however, I want the report to show "February". It needs to assume that each coke I sale is the oldest one purchased. And so, the first coke I sold was the one I purchased in January.
So here's my current setup and then I'll explain my problem.
I have joined the two tables together by their "product_id" fields. The join type is "Indeterminate". Following the example above, I record a transaction in the purchase table in January that I purchased a coke. That record contains the transaction ID (which I can't make unique for other reasons), the product ID, and the transaction date. Then I record another transaction in February with the same information. At this point, I now have two records in my purchase table and each of them have "Coke" as the product id. And later, in March, I add another of that same transaction. Now I've sold a coke so in my sale table, I'll have that same information - transaction ID, product ID, and transaction date.
I created another field in each of those tables called "flagged". It's a "yes/no" field. I was going to flag each transaction that had a match in the other table. So to continue with my example, I want a query to take the first sale of product "Coke", find the oldest transaction for that product in the purchase table (would be the one in January), and I want it to update the "flagged" field in both of those tables for those matching records to "yes".
To make the report work, I was going to look at the oldest date for each unflagged product. If it's flagged as "yes", it means it was sold so it would no longer obviously be a product on hand collecting dust. So the report finds all of the "Coke" purchases and gets the date for the oldest "Coke", now February, for the report.
So here's the problem.
I'm using an update query to change the flags from "No" to "Yes". The problem is that I've only sold one coke but all three cokes are being updated to "Yes" in my purchase table. I need it to only update the oldest record. I tried creating a select query and limiting the results to "1" record but that sure didn't help. Ha.
So should I be changing my query, my relationship, or both?
I need the record for the sold coke in the sale table to update to "yes" and the record in the purchase table for the coke bought in January to update to "Yes" without updating the coke purchases that happened in February and March.
Any ideas?
Thanks,
Magus
Sign In
Create Account
