[Yoshiaki]

Hi,

This is my first post here and is quite new in Access. I have searched for various sources of information but I still do not quite get it. I came upon this forum by chance, and hope that you guys can help to solve some of my doubts.

1. Say, there are these 3 tables - Inspector (One who do the inspecting), Audited_Form (The form used to audit operators), Flight_Audited (Flight which they audit on)and Operator (One who are being audited).

Hence;
Under Inspector, the fields are InspectedID, Name.

Under Audited_Form, the fields are FormID, InspectedID, OperatorID, Organisation, Date, Time, Questions, ...etc

Under Flight_Audited, the fields are FlightID, Flight_Details, Date, OperatorID
(Both the "date" in the above 2 tables are the dates the inspector do the auditing)

Under Operator, the fields are OperatorID, Name, Organisation

1 inspector can used many forms to audit on 1 operator. 1 operator can be associated with many flights.
(I hope I did not make you guys confused, will try to make a print screen asap as the database file is not with me currently)

In this situation, does the ID serves any purpose other than as a unique key? For example, if my supervisor now keying information, does he need to look up for which inspector is which ID?

2. If i were to create a form for easy keying of information, how to input multiple tables? I've looked up the web, and it actually mentioned about subform. I tried, but I failed.

How do I create a form that when a user keys in data, the data will be transferred to all the 4 respective tables? Instead of using ID, can I replace it with name instead? Cause I don't think my supervisor will remember the ID number of that particular operator he audited on, and he will rather key his name straight away.

Am I on the right track? If there are any parts that you do not understand, I will try my best to explain it.

P.S. Pardon for my English, as it is not my native language, and for such a long post.

[Ziggy1]

Originally Posted by Yoshiaki

Hi,



In this situation, does the ID serves any purpose other than as a unique key? For example, if my supervisor now keying information, does he need to look up for which inspector is which ID?

The ID serves as an index but would not nescesarily prevent duplicates, you should have a field related to your business that uniquely identifies the inspector, and set that field to not allow duplicates


2. If i were to create a form for easy keying of information, how to input multiple tables? I've looked up the web, and it actually mentioned about subform. I tried, but I failed.

You need to search about Subforms, as there are many examples, in principal, the sub has a related ID which is used to keep the sub record in sync with the main record...1 main to many subs... check on Primary/Foreign Keys). also keem in mind you don't always need to enter them all at once...take the operator table...you enter all the operators prior, and then create a lookup in the Audit form where you can select the operators..

How do I create a form that when a user keys in data, the data will be transferred to all the 4 respective tables? Instead of using ID, can I replace it with name instead? Cause I don't think my supervisor will remember the ID number of that particular operator he audited on, and he will rather key his name straight away.

Read up on Relationships, you need to link key fields. In your form you have ID and the name, you can bind (control source) the table to the form and then display the records. I can't really teach you from scratch, so you need to study some examples. Access can generate sample databases, look in the templates, these will provide good examples


Am I on the right track? If there are any parts that you do not understand, I will try my best to explain it.

P.S. Pardon for my English, as it is not my native language, and for such a long post.

[OBP]

Welcome to the Forum.
As ziggy says he can't teach you from scratch, but I can point you at a couple of threads on here where Databases like yours were built from start to finish with the help of the posters on here.
If you still can't get it i do free one to one training off forum via email.
One of the threads (actually more than one) is about a Movies Database by computerman29642, so if you search for him using the Advanced Search you can find them
this is one prior to the movies database
http://forums.techguy.org/business-a...-database.html
and this one
http://forums.techguy.org/business-a...bed-forms.html
and this
http://forums.techguy.org/business-a...03-report.html
and this
http://forums.techguy.org/business-a...3-amp-amp.html

They all contain questions and answers with examples of how to build databases.

There is also one other but I haven't found it yet

[slurpee55]

Yoshiaki, welcome to the forum!
One thing that we can help you with (and here I can say we - OBP often takes care of the complex stuff) is in the basic layout of your database. If you zip and upload what you have (no confidential data, please!) and perhaps graph out the relationships you want as well, I can assist you in building the tables you need and the queries that you should base your forms on.

[OBP]

Here is the other one
http://forums.techguy.org/business-a...tached-db.html

[Yoshiaki]

Thanks for all the reply!! You guys are really helpful, appreciated it! Will look at the links you guys gave and studied what you all have said (Won't waste you guys effort!).

By the way, I have also asked around. And I heard that other than using subform, I can use tab control, which seems more simple for me and for the user. (Oh, there is also one thread about tab form!)

I will try to post the database on next Monday, in the meanwhile, will read the comments and threads first.

And, thanks again!

[OBP]

Yoshiaki, with a Tabbed form you will still need "sub forms" for the other Tables, they just go on the tab instead of the Form details section.
By the way you are not "wasting" our time or effort, it is why we are here.

[Yoshiaki]

^Thanks, OBP!

I have did a print screen for my tables and also attached the database file. I changed the some of the fields due to some confidential.

Here it is!


I think I got my relationship of table settled (Hope so). Now that I will just need to create a tabbed form that consist of all these tables. I've seen computerman29642 first and second database, and I must say, it looks difficult! X.x"

[OBP]

Yoshiaki, your tables and Relationships look good at first glance.
Would you say that the Audit Form, the Refuelling form or the Inspector should be the "Main Form" with the Tabs on.
i.e where does the process start, with the Inspector, the Audit or the re-fuelling?

[Yoshiaki]

Oh, it should be the Inspector -> Refuelling Operator -> Flight_Audited -> Aircraft Type -> Refuelling_Operation (Last one).

Erm... did I answered to your question?

[OBP]

Yoshiaki, yes thanks. I have the Grandchildren coming this afternoon, so I will have to get back to you tomorrow.
It is quite possible that you won't need tabs for each of them, as they are closely related it may only take 2 or 3.

[Yoshiaki]

Sure, take your time and have fun with your grandchildren!

Meanwhile, I will also try to read more on tabbed form!

[OBP]

Yoshaki, here is a first draft, the Form is your Inspectors form with a Find an Inspector Combo box.
It has a Main Tab for the Audit itself, where you can select a flight and a Refuelling operation and enter the audit details.
You then have a Flights tab showing all flights, it could just show the detalis of the Flight chosen in the Audit, it has a Combo to select the Aircraft.
You have a Refuelling Operations Tab showing all refuelling ops, as before it could just show the details of the Audited operation.
Finally you have an All aircraft Tab showing a list of Aircraft.
I would suggets that you have a separate Table for the Organisations on the Refuelling Operation so that they can also be selected with a Combo.
I have made one change to your relationships, the Aircraft Table is now related via an AircrafttypeID field, as it should be related using an ID and not text, like all your other tables are.
I will leave any formatting to you.

[Yoshiaki]

Wow!! It's what I want... Thanks! But, erm... how do you make it? As in making all the tabs, and the prompt up when you open the database...

Actually other than this audit form ('Cause this is auditing on the refuelling), there are still other audit forms (Which i have just established the relationships). So, the forms will just be similar as the one you did, am I right?

Say, after I created the 6 audit forms in access. Can I also make a prompt up that will let the user to choose which of the 6 audit forms to go?

(Sorry with the more questions)

**EDIT**
I just google about the prompt up thing, it seems that one will need to input some codes? But where we should put these codes?

[jimr381]

To insert extra tabs you right-click on the tab control and select "Insert Page." I missed what version of Access you have but you can change the startup options via "Office Button">>"Access Options">>"Current Database" and change the display form on startup. In 2003 I want to say it is under "Tools">>"Database Tools">>"Startup." But all of that is off the top of my head.