trouble with tables and relationships

[Hoggster]

Hello,

I'm not that new to db design (started with access and migrated to fm) but I'm having a bit of a senior moment. I have two tables. One is an inventory of equipment, the other is of staff members. I want to create a table from these two that has one record for each staff member for each piece of equipment with additional new fields for whether the staff member is trained in its use. So if i have 20 staff and 300 pieces of equipment i'm expecting the new table to have 6000 records. the staff table would also have regular additions and the equipment table less so.

I understand relationships and in ms access i would do this with an append query to new table but i just can't figure it with fm 12.

can anyone help???

many thanks

nick h

ps i could attach the tables if required. many thanks

[comment]

So if i have 20 staff and 300 pieces of equipment i'm expecting the new table to have 6000 records.

I don't think that's necessary. You only need to record staff's proficiency, not the lack thereof. IOW, if each staff member is trained in using 5 pieces of equipment, your Proficiencies table would have 100 records.

[Hoggster]

Thanks for the reply but I actually need to see who has not been trained as well so we can train them!!

thanks

[comment]

That should be quite easy to set up using a ≠ relationship - no need for 5900 records of nothingness.

[Hoggster]

The aim is to have a layout for a member of staff which references the inventory table via a portal so that each member of staff sees only their records. They/we have to see all records of either trained or not trained. Is there an equivalent of a make table query in filemaker?? that i could work with :-)

[comment]

I am not familiar with Access, so I cannot answer that. However, I am quite confident that in any relational database, one should record facts only. What you are proposing is equivalent to 5900 journal entries of "nothing happened today".

[Hoggster]

Hi again,

i'm pretty sure i'm not dumb and i'm even more sure that you're not so something is being lost in translation.

Databases do contain facts but if a trainee has not received training on a piece of equipment then this is also a fact ( a false in access).

so the staff table would have a name,grade and a gmc number(unique identifier) and the equipment table would have manufacturer,model,location,image and a checkbox for trained or not. the combined table would have all of the equipment fields and a gmc field.

the staff table gmc would be related to the combined table gmc but i dont know how to populate it with the equipment table field contents.

is this any clearer or am i going to have to go back to access?

many thanks

[comment]

if a trainee has not received training on a piece of equipment then this is also a fact

Well, it depends on how you define a fact - so let me be more precise in my terminology:

If you accept a starting point of "no one is trained for anything", then a record of "person A was trained in the use of machine X" is information. It tells us something we did not know before. OTOH, a record telling us that "person B is not trained in the use of machine Y" does not carry any information: we already know this from the absence of a record telling us otherwise.

This is very similar to recording the attendance of students at a class. You can record the people attending or the people being absent. Recording both creates a redundancy. Or, if you prefer a more illustrative example, you do not need to play a CD of "nothing" to make your stereo system silent.

the equipment table would have manufacturer,model,location,image and a checkbox for trained or not.

I am afraid that does not make sense. A piece of equipment cannot be "trained or not". Similarly, staff cannot be "trained" of themselves - they can only be trained to use a specific piece of equipment (or several). This is a many-to-many relationship and it requires a join table.

I thought this much was clear, and that the only difference between us was how many records need to be in the join table: I say 100 (one for every occurrence of a person being trained to use a piece of equipment), while you say 6000 (a Cartesian product type of join).

[Hoggster]

The purpose of the project is to identify gaps i.e.; to find the nothings so the missing 5900! i dont want to train staff already trained. the difficulty is that the staff do not know what they are trained upon (there is a multitude of infusion pumps and ventilators for example) so i have to be able to show them each piece of equipment for them to determine if they are familiar with its use.

i have a sense that access may have provided the tools to develop bad habbits or clouded thinking which is why I'm having trouble with what you're saying.

i have to know a yes/no answer for each staff member against each and every piece of equipment. not just the yes's.

you mentioned a join table..could you clarify?

thanks

[comment]

i have a sense that access may have provided the tools to develop bad habbits or clouded thinking which is why I'm having trouble with what you're saying.

Alas, Filemaker provides no less opportunities for the same...

i have to know a yes/no answer for each staff member against each and every piece of equipment. not just the yes's.

How would you want the display to be built? For example, you could browse the equipment table and see in a portal all staff that are trained to use it, with another portal showing all those that are not (note that you do not need the 5900 "nothings" in order to build such display). Or you could show a portal of all employees, with the trained employees being conditionally formatted to stand out.

At a more basic level, you could simply perform a find for employees that are trained on equipment XYZ. Then do Show Omitted Only to find those that aren't.

you mentioned a join table..could you clarify?

See a basic demo here:

http://www.fmforums....hp?post/246136/

Then have a look at something that's quite close to what you describe:

http://fmforums.com/...hp?post/233897/