Best Type of Qry ? Data from 2 tables append to first in new field...

[roxannep]

What I have:

Table: Inventory
Fields: EndDate, Location

Table: Location
Fields: LocationID, BoxIdentifier, StartDate, EndDate, WeekNo

Table: Package
Fields: PackageID, PackageType

The user is selecting the package type, and entering an end date. They need to know based on those parameters which holding box (identified by BoxIdentifier and WeekNo) to then put items in and the type of packaging when they go to retrieve it they are looking for. This needs to show on their form right after they enter the Package type which I have set up as a ComboBox where they make their selection.

What I need:

Update into the Inventory Table, Location Field a new piece of data combining the BoxIdentifier, WeekNo, PackageType using the EndDate of Inventory to select this data based on it falling between StartDate/Enddate of Location. I haven't even added the package type yet to confuse myself even further, so right now I just want to see the BoxIdentifier and WeekNo from Location table in the Location Field of the Inventory Table.

I tried an update Query, but it didn't add this information into the table.

UPDATE Inventory, Location SET Inventory.Location = [Location].[BoxIdentifier] & [Location].[WeekNo]
WHERE ((Inventory.Enddate Between Location.Startdate And Location.Enddate));

Maybe an Update Query isn't the place to start? Any help would be greatly appreciated! Thank you! [sig][/sig]

 

[DMac]

Wow, I'm not sure why you want to do what you're trying to do but you can only have one table in the update statement main clause. i.e. it has to be Update Inventory Set ...

You then can use a straight select in the right hand of the set clause along the lines of;

Update Inventory
Set Location = (Select BoxIdentifier + Weekend
from Location
where Inventory.EndDate between
Location.StartDate and Location.EndDate)
where ???? unique identifying clause ????

The reason I'm confused is that nothing seems to uniquely identify the location record that you are trying to update. That has to appear in the where clause. [sig][/sig]

 

[roxannep]

I wasn't clear in stating them: The unique identifiers in Inventory are InventoryID (autonumber), in Locaton, LocationID (autonumber) and in Package Type, PackageID (autonumber). The problem is these identifiers do not mean much to the various data entry operators that come and go here. LocationID 5392 doesn't give them the information they need then reading it manually from a label and not having access to a terminal to verify what that means.

They asked if the Location information stored with the Inventory Item could be an identifier created from the BoxIdentifer (letter) of which there are only 6 at a time, the WeekNo (which is generated after they enter the "end date&quot of which there would be 52 per year, and the Package Type which would only be 6 types, but because of the variety, this assists the shipper in knowing what to look for. On the labels that are generated, they want them to read something like "A34 - Book, 10/06/00"

I will begin with the code you posted and see if I am able to have it happen with this starting point. My apologies for the confusion, I'm still learning how much detail and how to present the question when it gets this complicated. Thank you for your help! [sig][/sig]