Appending inventory records with current price records

[sberti]

I hope I can explain what I'm wanting to do.
My question is about database design.


My company is a farm, not a retail store. (Just so you understand the lack of precision required.)

The big picture:
We Receive Multiple Types of Feed Inventory monthly
We Feed Out Multiple Types of Feed Inventory monthly

We do not have a physical method of tracking exactly the Feed Received to the Feed Fed Out, but want to 'generally' track the cost of the Feed Fed.

Connecting up the most Recent Price of Received Feed with the Feed Fed Out is close enough.

Constructing the database:

I have one table for 'Monthly Feed Inventory Received with Prices' records.
I have another table for 'Inventory Fed' records.

The tables are updated at month end.

I can't just associate the 2 tables by the 'month' because we might received Inventory-A in Feb and still be using it in June, without having received any in Mar, Apr, or May.

I've pulled my Newest Prices from the 'Inventory Received' table by a couple of queries making use of a Max Date. So now I have a table with just all the Newest (Current) Prices.

I have associated the Feed Fed table with the 'Current Prices'.

I can't just run each month, because we will lose all our historical prices.

I've made an append query to put my 'Inventory Used' together with 'Current Prices' in a new table.

It's working. We now have a record of the 'Feed Fed' with Prices for each month.

But I get the error messages that Access can't append all the records because of Key violations. That's doing exactly what I want it to do, because I don't want it to change the existing records in the Append Table or make duplicate records.

I'm wondering if there is a better way to do this? I'm not sure this is 'best practice'. If there is not a better way to do this, I'll just have to suppress the error messages.

I'd welcome any suggestions.
Sandi

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post.

[orange]

It may be best to post a copy of your db as June7 has suggested.

I wrote something recently where someone was recording the Price of an item in the Product Table. The problem was that when there was a change in the Price, all of his existing records were affected.

I know this isn't the same context as you have, but here is what I wrote and I hope it is useful to you.

.....It's much like the Cost of a Product. If you store the cost in the Product table, then how do you change the cost without affecting all previous Sales?? In an Order or Order Detail you can identify the Product and the Cost at the time of the Sale (such a cost could reflect a discount; a preferred Customer price; a clearance Sale; damage Goods mark down...) and would not be associated with Product generally....

You may have a structure issue, but your database and the issue (in detail) may help us resolve/confirm.

[sberti]

Thanks for your replies.
Before I try to upload a copy of my db, I'd just like to ask a general database design question. Maybe I can ask in a clearer way:

There are 2 tables that are each updated monthly.
I want to keep the 2 tables intact.
I also want to join a few (not all) of the fields from each table into a 3rd table using only the New Records each month.
Is there any other way to do that other than an Append query?

I have done something similar before, but did not keep the 2 original tables intact. I appended ALL the the fields from both tables into the 3rd table and then deleted ALL the records in the 2 original tables, so each time I ran the Append query the ONLY records in the 2 original tables were the New Records. Of course, I didn't get any 'key violations' error when I did it that way. It worked fine.

This time, I want to keep all the records in the 2 original tables (because I'm not putting ALL the fields in the 3rd table).
I've made sure I have the Key set up in my 3rd table to prevent duplicate records.
When I run the Append query, it works. It does exactly what I want it to do. The result is that only the New Records are appended.
Of course, Access tries to append ALL the records (new and old), but since I have the Key to prevent duplicates in Table 3, it only appends the New Records. Exactly what I want. It works.
The only thing that annoys me is the 'key violation' error message. The reason it comes up is because Access is trying to append ALL the records from Tables 1 & 2. The Old Records are already in Table 3 so can't be appended (nor do I want them to be appended). I can live with it since the query is producing the results I want, but was just wondering if anyone has a different way of accomplishing the same thing, without producing any error messages.
Thanks.

[June7]

Why do you need the 3rd table? Why not just a query of the 2 tables?

Filter the records so that only new ones are included in the append action. How? Need some field in the tables that can be used to identify new records. Either a date or a Yes/No would work.

[sberti]

Why do you need the 3rd table? Why not just a query of the 2 tables?

Filter the records so that only new ones are included in the append action. How? Need some field in the tables that can be used to identify new records. Either a date or a Yes/No would work.

Ideally it would be very simple if I could query the 2 tables using the [Dates] as joins, but the 'Feed Received Dates' don't match up with the 'Feed Fed Dates', so I can't query the 2 original tables by [Date] or even [Year]-[Month]. The only fields that both table have that I can join on are 'Feed Type'.

I have to append the result to a 3rd table because I'm using the Max Date so it always calculates on the New Prices. I don't want to look at the data for 6 months ago and have it calculating with the Current Prices. I append to a 3rd table to preserve that monthly data.

I could filter the tables for the new records, but that would mean toggling all the Feed Types and all the Feed Prices Yes/No every time. I think it's easier to just click once in the error dialog box (even though I hate seeing that error message).

I appreciate your suggestions.

[June7]

Have the 'AlreadyAppended' field DefaultValue of No. Select only the records with No for append. After the append, run UPDATE to set all records to Yes in the field.

I think a date field is more informative.

[qa12dx]

u need to relate it to batch number.
in ur inventory table have a collumn for batch number, eg - dt, productID, batch#, qty, price
in ur feed table use this batch number - dt, productID, batch#, qty

in query join inventory table and feed table on productID & batch#. Select feed table - dt, productID, batch#, qty, inventory table - price
only thing to remember is units of inventory table and feed table must be same. so if u feed in kgs, create a unit price field in inventory table and run a update query to calculate unit price.

if u recieve in tons break it down it kgs... hope it helps....

[sberti]

Thanks for the time you've taken to help. I'd like to use batches, but that doesn't work in our 'real world'.