Access index problem

[Tomino]

Hi all,

I have a problem with indexes in tables. I made an inventory DB where we can store items on several locations. Their are several difference items, each kind of item has his own table because they all have different fields that the user has to fill in.
The only thing they have in common is the place, they're all stored in the same room, which has numbered racks and each rack has numbered spaces (for example: Sample X is in Room 1, Rack A, location B)
The combination of Room-Rack-Location has to be unique, this I can do in one table by using indexes, but how can I combine the indexes of several tables?
Do you think it is better to keep the track of place inside another table but how can I do this?

Thanks for your assistance!

Tom

 

[gemma-the-husky]

i would be inclined not to have separate tables for inventory type, and keep everything in one table, even if there are some blanks

this may be an occasion when one-to-one joins might be useful to deal with the variant fields.

i just think it would be easier to handle locations/movements etc if you are only linking one table - the other fields are probably attributes that can be set/ignored as approriate. You will have more than enough issues to worry about without bothering about handling multiple inventory tables

eg you wouldnt have a different table for a washing machine and a fridge, just because a washing machine has a variable spinner speed, and a fridge doesnt.

 

[Dennisk]

all you need is a compound index.

Step 1 give your index a name then on the same line select one of the columns.
Step 2 on the next line select only another column so this and the first column are grouped under the original name.

 

[Tomino]

Thank you both for your reply, it's really appriciated.

@gemma-the-husky: I am also a big fan to keep all in one table, it's much easier to program but it gives me some issues where multiple tables can be a solution. For example:
- Lets assume the user wants to put sample x in room A, rack B in location C: the info for this is in tbl_sample_x
- Another user wants to put sample y in Room A, rack B in location C: the info for all the y samples is stored in tbl_sample_y so the program doesn't know that this space is occupied, it will put the sample in that location.

If i put all sample in one tbl, the disadvantage is that I have to allow to let some open spaces inside the table, can I oblige the user through form to fill in spaces instead of obliging it through table properties?

@Dennisk: with compound index you mean to combine several fields of one table in the same index, correct? I would like to combine several fields across multiple tables inside one index, is it possible?

 

[Rabbie]

.

If i put all sample in one tbl, the disadvantage is that I have to allow to let some open spaces inside the table, can I oblige the user through form to fill in spaces instead of obliging it through table properties?

Yes. You can validate the input data from your form using the Before Update Event

 

[gemma-the-husky]

if i may

@Dennisk: with compound index you mean to combine several fields of one table in the same index, correct? I would like to combine several fields across multiple tables inside one index, is it possible?

indexes enable fast retrieval of records within a table, and prevent duplicates where appropriate. tables are linked together, so a key in one table is an attribute of another, which then has its own key structure

i think you are either missing this distinction, or have some denormalised data, if you think you need to combine indexes across tables