Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Access 2010 - Update matching fields in two tables.


  • Please log in to reply

#1
magusbuckley

magusbuckley

    Member

  • Member
  • PipPipPip
  • 625 posts
Hello to all.


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
  • 0

Advertisements


#2
magusbuckley

magusbuckley

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 625 posts
As a quick update, I might mention that it is quite possible I'm making this more difficult than it needs to be. I actually have a transactions detail table that stores all transactions - purchases and sales - and their details so, it's purchases and sales in one table. I could add my "yes/no" flag field to that table if there is a way to update it from within a single table but I'm not sure how to do that or if it's even possible.

If there is an easier way to do this, I'm certainly up for suggestions.

For now, I just wanted to make to mention that transaction detail table.

Thanks,

Magus
  • 0

#3
magusbuckley

magusbuckley

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 625 posts

Well, I'm sure there was a much easier way, but I wound up creating some extra tables and queries to get this done.  It turned into a big, complicated mess but I got it done.  Ha.

 

Thanks,

 

Magus


  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP