Making a form populate two tables

[coffeeman]

I don't know if this is the wrong way to go about this or not, but here it goes.

In our Access database, I have figured out a way to have a certain material get received in access the day it was received with a lot code number. I then input daily production and have a form setup to pull the specific lot code of a product by an SQL query that looks up the Receiving Log table and pulls the lot code that I have selected under "CurrentLotCode" column.

Over the years this data has piled up. For a certain material (A box for example), I have 40 entries for it, each with a different lot code. Once we run out of a particular material, I then can select the new lot code number under Current Lot Code (Yes/No, checkmark) column.

I want to keep the history of shipments/receiving of material, but I want to be able to view a condensed table with less entries of each material and be able to select the CurrentLotCode from recent entries of a material.

Is there a way to produce a separate similar table that I can manually delete the ones that are old, but never delete entries out of the other one, so it will be used for history purposes.

Any suggestions will be helpful. Sorry for the lengthy question.

Thanks for the HELP!

 

[pbaldy]

I wouldn't think in terms of 2 tables. I'd have the data in one table, but use a query that restricted the records returned for your "condensed" view.

 

[coffeeman]

Ah. I see. How exactly could I run a query to make the condensed view to show the 3 most recently received Lot Codes of each material?

Thanks again for your help.

 

[pbaldy]

This type of thing?

http://support.microsoft.com/kb/153747/en-us

 

[coffeeman]

The issue I am having with either one of these examples is that I am only pulling from 1 table and these examples are pulling from 2. Do you know of a simpler way to go about it? Is there a Criteria I can enter in the Query that will only show the last 3 most recent entries for a material?

Thanks again pbaldy. You really know your stuff.

 

[pbaldy]

Can you post a sample db?

 

[coffeeman]

I will try to attach the sample. I cannot send the whole database due to privacy with customers. In the database, you should see the table that gets populated, Boxes/Bags: Receiving Log. (Forgive me, this database was written by someone else in 1997 that had no idea what the rules were as far as naming of tables goes). I need to create this query from this table.

Thanks and let me know if I posted it correctly. I made it a zip file.

 

[pbaldy]

This kind of thing?

SELECT *
FROM [Boxes/Bags: Receiving Log]
WHERE [Boxes/Bags: Receiving Log].[Date Received] In (SELECT TOP 3 Alias.[Date Received]
FROM [Boxes/Bags: Receiving Log] AS Alias Where
Alias.[Product Description]=[Boxes/Bags: Receiving Log].[Product Description] Order By [Date Received] Desc)
ORDER BY [Boxes/Bags: Receiving Log].[Product Description], [Boxes/Bags: Receiving Log].[Date Received] DESC;

 

[coffeeman]

i put this in the criteria field under Product Description in the newly created query. It highlights the SELECT and gives an error.

Is there something I am doing wrong? Do I need to enter it somewhere else?

Thanks.

 

[pbaldy]

That was an entire query, not a criteria. Start a new query, get it into SQL view, delete whatever is there and paste that in and run it.

 

[coffeeman]

I know I have said this before, you are smart as heck. Thank you again for the help. This definitely works!!

 

[pbaldy]

I alternate between "brilliant" and "dumb-as-a-post"; you just caught me at a good moment!