Display values in a grid and drill-down to show another table

[science_nerd]

Hello, very amateur Access user here.
I have 4 large freezers that I store a defined number of boxes in, and I need to create a map for them. Each freezer has a different configuration but all follow the same format of a number of shelves, each shelf has a number of racks, each rack has a number of drawers and each drawer stores a number of boxes. I would like to map these in a 2D grid and then be able to double-click on a box to bring up a list of the box contents. I also need to be able to change the box's location easily (eg cutting and pasting) and delete a box and it's contents.
How can I do this?
A cross-tab report displays the boxes well but it is not interactive
Thanks in advance

[Homegrownandy]

can one item be in more than one box?

this may seem like a silly question. I mean 1 item that there may be multiples of. Or is each item unique?

[Homegrownandy]

if you're intent on achieving the 2d visuals of it. what you can do is:

have a form that is just a grid of buttons. Each button opens a form that displayed only the information you want. (each button represents a box.)

The issue you will have is making querys work. you need an update query that will change the box_ID for all items in box_ID whatever. If you look on youtube at select querys and update querys you will be well on your way. I think its better you come back with specific issues you have getting to that end point.

[science_nerd]

can one item be in more than one box?

this may seem like a silly question. I mean 1 item that there may be multiples of. Or is each item unique?

Thanks Homegrownandy. Yes there are multiples but they have been entered as separate records because their Box_Name plus Position in the box is unique.

I am not keen on creating individual buttons or queries for each box as there are 1000 boxes. Also boxes change regularly and this database will be managed by many people with no understanding of how access works so I need to make it very user friendly to make changes

[Homegrownandy]

how do you propose to update/display anything without querying the data?

You asked for a 2d grid to show every box with a click event that displays that information. You have to define what that clickable area is even if you're not keen.


and finally. No user will even see any of the querys they are assigned to record source/ click events/ whatever.

There's no getting around the fact that you do need to separate this data. Without knowing the structure of your database I cant offer any advice on how to achieve this beyond the general comments I've made.

Can you post a screenshot of your entity relationship diagram?

[Homegrownandy]

Just as a note. One item only needs to exist once in the database. Even if it exists in 100 places in real life. You chose the relationship via a junction table. (may be worth looking into this also)

so.
Item number 1 exists in box 5 rack 3
Item number 1 exists in box 3 rack 5

You can keep adding them individually also I'm sure you wont have an issue unless there's tens of thousands of items. But you don't need to define the location of an item in that items record. A junction table will show all relationships between items and location.

[Homegrownandy]

this is an example off google. ONE actor appears in several films. The table between defines that relationship.

[science_nerd]

Ok I think I misunderstood initially. So the queries would be to retrieve the box that is in that position, and the positions don't change, just the box name and contents.
I will post the relationships when I'm back in the office tomorrow

[Homegrownandy]

yes youre just asking the database to show you whatever information you want to see. you can set this up however you want.

an update query is exactally the same but you can update it also. (any part you like) could be box_ID, Rack, whatever.

[science_nerd]

Here are the relationships

[Homegrownandy]

Just a few notes:

If you want to define the type by storing that 'types' details on another table than it would be better to link them via an auto number ID field rather than the name. You can do it this way as long as the name is unique, but when all youre storing is the name itself you may aswell store that on the sample table.

same is true for patients and studies.

The relationship between samples and time points: If multiple time points exist for the sample then yes you can use this table (junction table to show the multiple times). this is not how you have it set up.

Time points needs its own ID for each record. you are using the foreign key patient ID as a primary key. this wont work.

Assuming you fix that issue. Then, I'm sure the relationship would be one sample has many time points. not one time point has many samples. (this may be the case but is that what you're interested in?

Its important to get these things right so everything you do works correctly.

Andy

[science_nerd]

Hi Homegrownandy,
Thanks for looking at the relationships and sharing your thoughts. It's good to have input on whether I have set the database up properly.
For the "types" table, I created this so that I could choose the sample type from a drop down menu. Do you mean it would be better to autonumber the types? When you say I may as well store that information on the sample table do you mean as a text field? Or still make a combo box and enter the types manually into the wizard?

As for timepoints, one timepoint has many sample types, then each sample type has many tubes which are stored in different boxes and positions within the box. I was thinking that the "samples" table is a junction between Timepoints, Types and Boxes?
I'm using the combination of patient_ID and Sample_Date_Time as the primary key for the timepoints table - is this no good? I did start out with autonumbering the timepoints but I didn't know how this would work with importing my existing data.

[Homegrownandy]

if you create a drop down using the ID and then also the text you want to show; you have the option to hide the ID field. the user just sees the text list you want.

you then save this information in the appropriate field. (it will save the ID of type).personally I would give time points its own ID. But you are correct the ID can be a combination of the two.