How can I fix these duplicate entries

Could someone please look at this db and tell me how I can possibly make the subform pull only the latest price that is not newer that todays date. I know I can use the max function in a query but then the subform becomes uneditable. I need the subform to be editable. I thought about storing the latest cost in the parts table but I don't know anything about append or update queries. I think I would need one of these to keep the cost in the parts table the most current.

Thanks in advance

I didn't look at your db but I will tell you how to solve the problem. If you keep prices by date then you need to include an end date as well as a start date to make your queries simple. Then the where clause is:

Where Date() Between PriceBeginDate And PriceEndDate;

Date() is the function for today. If you want a price for other than today, then just use the field name that holds the date.

Confused

I appreciate the response but I don't think that will solve my problem. I know I can use queries to pull the latest not future cost of a part and display it in my frmpartssubform but then the form becomes uneditable. I tried using queries to filter out the current cost of the parts, then use a maketable query to store the current cost but I feel like I am duplicating information. I need to figure out how to keep the form editable but still display the correct prices. Maybe it's just a simple form filter on the date field, But I can't seem to figure it out.

This is what I have so far.

You have several problems.
!. UID should not be in both the parts and cost (and cost history) tables unless it is part of the primary key of one and part of the foreign key in the other.
2. you have no primary key defined for the current cost table.
3. the current cost table is local so you cannot enforce RI with the rest of the tables.
4. The relationship between parts and current cost is 1-1 so there is no reason for the current cost table.
5. The query that you use for your form needs to include the primary key of the both tables since the part number autonumber id needs to be copied (by you in code or a macro) to populate the primary key of the current costs table. If you notice, you can update data with this query but you cannot insert because you need to insert into two tables and you have provided the key for only one of them.

See if you can get some of those things straightened out.

Pat Harman...

I have taken what you have said line by line and this is what I have come up with...

1. UID should not be in both the parts and cost (and cost history) tables unless it is part of the primary key of one and part of the foreign key in the other.
-If you are referring to UMID then- The tblparts stores information on parts and there current cost. This is the table that my frmpartsubform is based on. The tblcosthistory is the table that stores any changes in price. If a price is changed so could the UMID. (ex: Peanuts $1.20 lb vs. Peanuts $4.00 Box) Therefore I believe I should be storing the UMID in both tables. Is this what you meant?

2. you have no primary key defined for the current cost table.
3. the current cost table is local so you cannot enforce RI with the rest of the tables.
-The only purpose of the tblcurrentcost was to temporarily store the cost and umid so I could have an update query to update the tblparts with the recent price. I only did this because I am struggling with an non updateable query. Ideally I want to be able to store costhistory and pull the current cost from there and update the tblparts. But I don't understand queries enough I guess.

4. I agree there is no reason for the current cost tbl. The reason it's there is listed above.

5. I have deleted the current cost table.

Please take a look at what I have now.

I know it's not uncommon to store past and future costs. I think this should be done in a seperate table since you can have multiple history for one part. I don't know if it's a good practice to store the current cost in the parts table and the history costs in a seperate table, but I don't know how else you could do it and still make the form updateable. I am using a 2 query method to first get the current effective date of each part and then a 2nd query to join the relative fields (PartID, Cost, Umid) this data just needs to be updated in the tblParts BUT HOW?

I have attached the updated db

I won't have time to look at this for at least a week and a half. If you still have trouble, just pop the post to the top and I'll see it then.

1. If the price is dependent on the UMID then, UMID should be part of the primary key of the cost table.

2/3. The query IS updatable as it is written. You just cannot use it to add new rows for the reason I explained. If you fix the query, you can use it to add rows but getting rid of the table entirely is better.

Umid

I probably should explain that the umid is just a reference to the unit of measure table so I don't have redundant info. For instance:
umid unitofmeasure
1 = each
2 = box
3 = lb.
So the umid should follow the cost where ever it goes shouldn't it?

I will keep trying, I guess I am not completely following what you are saying. I think I have been looking at it to long.

Using the form wizard, I created a main form "MyPartMain" and a subform "MyPartSub" in your database. The cost field in the subform is updatable.

Perhaps you can try rebuilding your forms.

jon k

Rebuilding the forms won't fix this problem. You are not accomplishing what I want to do. Please refer to the original question.

-Quote-
Could someone tell me how I can possibly make the subform pull only the latest price that is not newer that todays date. I know I can use the max function in a query but then the subform becomes uneditable. I need the subform to be editable. I thought about storing the latest cost in the parts table but I don't know anything about append or update queries. I think I would need one of these to keep the cost in the parts table the most current.

The costs are stored in the tblcosthistory. There are multiple prices for every part, past and future costs, at that. I wouldn't even store a cost in the parts table if it were possible to pull the most recent, not future cost, from the tblcosthistory and populate the subform with it. Remember the subform has to be editable. In the db I have posted I have accomplished making a 2 query method pull the correct cost but it's not updateable because it has to be a totals query. I guess what I am asking is, is the only way to make a nonupdateable query update data in a table-- is to use a make table query, then and update query based on that table?

This just seems crazy to have to use 4 queries and a temporary table to get the current cost for something, not to mention how sluggish the db will become.

vba maybe

I am not a vba person by any means. Everything in my db has come from reading and the help of you people on these forums. I was thinking how powerful vba is, and, wandered if anyone has ever written any code to work around this issue? Maybe a module that finds the latest cost and updates the corresponding table rather than building all these parts for one little task.

If it's possible, I could just base the mainform and subform off the table and when the form is opened the code could pull the correct prices.

I feel like I am wasting everyone's time with my questions, but I am not finding any other relevant posts. I did read the help section and it stated:
'By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions. '

I am reading into this further, maybe this is a way out!!!