trouble with max date query

[JCM]

I have what seems a very simple issue but has been a major problem for
me to resolve...

I have 2 tables; 1 (widgets) describes my items in detail and includes
a unique primary identifier for each type of widget (not an
autonumber). The second (location) describes where the widgets are
stored. The 2 tables are linked by the primary identifier. The
locations table includes the storage room, row, shelf and box. My
database is set up so that new widgets are entered via a form (and
subform for location) and if they are moved from one location to
another, a new row is made with the new location in the location table.
Since this row has the date, a totals query with max date and the
primary ID joined to a current location query will tell me the current
location.

This part has been easy. The hard part comes when I move a box of
widgets. Each box contains many different widgets, though with some
similar aspect, and has a unique id. I have set up my new location
form so that I can enter the primary ID, the Box ID or both. The
trouble is that if I move an entire box, the primary ID field for the
new location is empty, and, as a result, when the query is run I don't
get what I want. The responses I get are myriad and depend on the
joins but so far none have been quite right.

Any suggestions would be greatly appreciated. I'm a advanced
beginner/intermediate with access.

 

[Jeff Boyce]

I can't quite tell if you are storing the WidgetID or the BoxID in the
Location table. Are either/both going into the same field in tblLocation?

Where's your table that shows which Widgets are in which Box?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JCM]

Jeff, tblLocation has fields for both widgetID and BoxID. WidgetID is
the primary key in tblWidget. A particular WidgetID and BoxID can
occur many times in tblLocation. TblLocation shows which widgets are
in which box, as box is the primary location for the widgets.

Thanks, JM

 

[Jeff Boyce]

If I understand your description of your "domain",

one Widget (i.e., WidgetID) can be zero-to-one Box (BoxID)
one Box can be in only one Location (LocationID)
one Box can hold one-to-many Widgets
one Widget can be in only one Location
one Location can hold zero-to-many Widgets (if in a Box)
one Location can hold zero-to-one Box

From your description, if you have more than one Widget in a Box, you have
to enter multiple records in the Location table. This seems
counter-intuitive.

Regards

Jeff Boyce
Microsoft Office/Access MVP