Relationships

[bill1282]

I am writing a access database for work(fire dept). It is a database for the vehicle checks. Most of the fields are Boolean fields we just need to know if the inventory is on the unit. I am kind of new to this. My tables so far are as follows:

TCardiacMonitors
TFirstInBags
TGlucometerKits
TIntubationKits
TUnitNumbers
TCompartmentNumberOne
thru
TCompartmentNumberEight
TSealNumbers
TEngineChecks

all list the inventory that is supposed to be in that table

Everything is dependant on the Vehicle it is on so my question is Do I relate everything to a look up field in TUnitNumbers(intUnitNumberID) or is there a better way to do this? I need to relate everything to the date it was checked

Thank you for your help Bill

 

[Mile-O]

Most of the fields are Boolean fields we just need to know if the inventory is on the unit.

You are creating a repeating group which is strongly not advised.

My tables so far are as follows:

TCardiacMonitors
TFirstInBags
TGlucometerKits
TIntubationKits
TUnitNumbers
TCompartmentNumberOne
thru
TCompartmentNumberEight
TSealNumbers
TEngineChecks

Okay, that's wrong.

What you want is one table called, for example, tblCheckTypes.

This has the following fields.

CheckTypeID - Autonumber, primary key
CheckType - Text

Now, rather than naming fields with your checks, you can add records (one per check) and your database will start to grow down, not across which is the objective. It also means that should some new item be added to the check you just add a new check record instead of adding a new field to the table, adding the field to all relevant queries, adding new controls to pre-designed forms and coding around them, adding new fields to reports, and updating any macros or other code.

Have a look at Pat Hartman's suggested structure for this sort of survey database here.

 

[quest4]

I like what you said, SJ. I would go a little futher, I would also add a checkbox, chkExist, a date field DateChecked, and a txtbox, CkeckedBy. Both the last two fields could automatically filled in when the check box is checked. That way you would know who checked what and when, it is the ISO in me.

 

[bill1282]

Forms

Thank you for your help. I have 117 records in tblCheckType I have to make a form that list all of these and the answers which I set up as yes/No and Not applicable in tblAnswerCodes. I also added tblParamedic which will list approximately 100 employees. I am not having any luck. Can you give me some direction with this? I am also going to add the suggestions by quest4 I think that is a good Idea

Thank you
Bill

 

[Rich]

I like what you said, SJ. I would go a little futher, I would also add a checkbox, chkExist, a date field DateChecked, and a txtbox, CkeckedBy. Both the last two fields could automatically filled in when the check box is checked. That way you would know who checked what and when, it is the ISO in me.

the checkbox chkExist is actually pointless and an unwanted overhead in this case.
You can simply look at the DateChecked field to see the status of the record.
If you want the procedure automated then just add code to the DateChecked OnClick event

 

[Mile-O]

and a txtbox, CkeckedBy.

Like Rich, I have a problem with this. Over time you will be duplicating loads of textual data. What is really wanted here is a numeric field bound to a combobox. The RowSource of the combo should be a separate table for auditors. Thus, should names change, the they only have to be changed in one place; in one record.

 

[quest4]

Morning Bill, in the tblParamedic what are you have problems with. Structure? Only need a few things in it MedicNo, employee number are also good. MedicName is the only other major thing, you can also have addresses and telephones for general use. Then Just add MedicNo the the desired table and go to the table design and select tblParamedic as the source. Don't worry about the no, we can easily have it desplay the name. I work in the world of ISO 9000, that is why I suggested what I did, everything must be trackable for us. Personally I think that is a good think, it helps when something goes wrong. If I can be of any further helpp, please just let me know. hth.

 

[bill1282]

I understand the table structure but I don’t understand how to list the 117 records on a form so that I can make a check sheet for the employees to check the vehicle every morning. The items have to be listed for that employee to be able to check the compartment to make sure they are there. Such as:

Compartment 1
Bag Valve Mask - check box or yes/no
Nasal Cannulas (5) - check box or yes/no
NonRebreather Masks (5) - check box or yes/no
Battery Date - date

There are eight compartments, first in bag and a drug box that have to be checked.

Thank you for your help
Bill

 

[quest4]

Bill, we look at data through forms, in forms we can create records, modify records and delete records, but it is all data. Form forms we can print reports and from queries we can print reports. As for what you showed me think about this:
In Table:
IDNo Autonumber(Pkey)
Compartment - Combo
CompartmentInspection - chkbox
DateChecked - Automatically filled in by the chkbox
CheckedBy - Automatically filled in by the chkbox

Display on Form or Report:
IDNo
Compartment - Select compartment 1 from pull down
Compartment 1 - chkbox DateChecked - Auto fill CheckedBy - Auto Fill
Bag Valve Mask
Nasal Cannulas (5)
NonRebreather Masks (5)
Battery Date - date
The same for all of the compartments, do you understand what I am suggesting? Do you see where this is going? Look at the size of the data now. It is getting late here and it is very close to Miller time, have a great TD. hth.

 

[bill1282]

Makes sense that is the way I will do it. Thank you very much you have been a great help.

Bill

 

[The_Doc_Man]

Bill, I'm going to advise you using the "old programmer's rule:" If you can't do it on paper, you can't do it in Access.

You need to look at the concept of normalization, which means you will end up with parent-child table relationships.

Like, each unit has a record in a unit table.

Each compartment has a record in a compartment table AND the compartment record links back (perhaps via unitnumber) to the unit table.

Each item you can have is in an Inventory table. The units link back to the compartment numbers. Depending on you record the compartment numbers, this could be a unique number or a non-unique number. In the latter case, you would have to include the unit number so that the combination of unit and compartment was unique.

NOW, the answer to where you put the date depends on how you run the check. If you check a compartment and it passes or fails as a whole, you put the date on the compartment. If you check the unit as a whole and it passes or fails as a whole, you put it on the unit. If the check the component and all you want is when you last saw that the component was in the unit and compartment, the date goes on the component. This is due to a little normalization rule that says you put something ONLY in a table where that something depends on the ENTIRE KEY of that table.

So for example, if you check by compartments, putting the check date on a unit is wrong (because you are lacking the compartment number in the unit table and you check by compartments in this example). Putting the date on a component is wrong (because the key of the component record has to include a key to uniquely identify the unit, compartment, AND component, but the date doesn't depend on the component in the example.)

Therefore, we won't know where to put something until you tell us where it goes. In which case, you won't need to ask because you know already. The little thought experiment I just gave you about where to put the date shows the principles involved.

 

[bill1282]

I want to thank everyone that is helping me out but I am still a little confused
Is this table structure correct?

tblUnitNumber
UnitNumberID AutoNumber PK
UnitNumber
DateChecked

tblInventoryItems
InventoryItemID AutoNumber PK
InventoryItem

tblUnitCompartmentNumber
CompartmentNumberID AutoNumber PK
UnitNumberID - PK (FK to tblUnitNumber)
InventoryItemID (FK to tblInventoryItems)

If this is write how do I get the InventoryItem to show up on the form?

I posted a reply on another thread(below) I can not get it to work.

Originally Posted by monkeytunes

2. The "proper" way to do it would be to run an APPEND QUERY every time you input a new participant. (This can be triggered via Visual Basic or Macros using the "After Insert" property/event on your main form.) In other words, every time you have a new record in the tblContacts, that person's ContactID is added throughout the AnswersID table as well, populating the table and leaving NULLs in the "Answers" field until you get around to filling those in.

 

[Mile-O]

If this is right how do I get the InventoryItem to show up on the form?

Make a query joining the two relevant tables and bind the query to the form; not a table.

2. The "proper" way to do it would be to run an APPEND QUERY every time you input a new participant. (This can be triggered via Visual Basic or Macros using the "After Insert" property/event on your main form.) In other words, every time you have a new record in the tblContacts, that person's ContactID is added throughout the AnswersID table as well, populating the table and leaving NULLs in the "Answers" field until you get around to filling those in.

WHAT?

 

[bill1282]

I need all the records to show at the same time. This is the thread that I found
http://www.access-programmers.co.uk/forums/showthread.php?t=69924

 

[bill1282]

Any Ideas?

Thank you,
Bill

 

[quest4]

Good morning, Bill. Once the table design is finalized, hopefully with the rules of normalization, and the relationsihips setup, then you need to design a form. The form will handle the entry of your data and any editing, also. The form can have its' recordsource either from table or a query, but append queries are not need at this time, they would be used to transfer your data to another table, like for archiving. The relationships are built in the tools pull-down, relationships menu, not in the query. I hopew this helps clarify things a little, but once the form is up and runing, the rest is easy. hth.

 

[bill1282]

Please let me know what I am doing wrong

Thank you,
Bill

 

[quest4]

Good morning, Bill. I looked over the relationships doc and everything looks good. In the table themselves, the ID should all be the PKeys of each respective table. By the looks of things I see I take it that tblSurveyys is the main table and everything will key off of SurveyID, is that what you are lokking for? So far I done really see anything wrong. Personally I would not of even used the relationships at this time, In the tblSurveys, by setting up the comboboxes there would be enough. Next I would build the forms for the tables so you can create and delete and modify thing, but don't do frmSurveys yet, sense it is the main form we will work with. I tried to attach a sample dbase, but I have not figured that out yet, the cmdButton is doing nothing for me. If I get it working I will attach it. Mean while carry on it looks like your moving in the right direction.

 

[bill1282]

tblSurveys should I make the comboboxes from a query such as
tblSurveys/ UnitNumberID - tblUnitNumber/ UnitNumber or UnitNumber

The attachment is what the final form needs to look like with the answer fields

Thank you
Bill

 

[quest4]

Comboboxes always get their data from either tables or queries. If your data needs to come from more than one table or needs to have a crieria applied, then it is a good idea to use a query. Remember your form does not need to look like your report, it just needs all the data that you want in the report. Like one thought for the form could be something like the site would be the main form and then you could have eight tabs and on each tabe have a subform for each compartment, this is just a thought for you you can ignore it if you like. hth.