A beginner Access problem.

[BrenOS]

Hi all. Had a quick peruse and this place seems really helpful, lets hope you can help me out here.

I have a project to do which roughly described is engineers doing jobs on aircraft. Three main tables:

Engineer
Booking
Job

The job table denotes the work required on an aircraft, with a unique barcode for the "job card". The Engineer table contains information about our engineers, with a unique ID.

Now here's my problem. The booking table needs to store information from both of these tables, but store it seperately. So I need to take some information from Engineer (ID, name, trade etc) and some from the Job table (barcode, description etc) and store it again in the "booking" table along with a few other "new" fields unique to the booking table. When this "booking" is made, it must record the start time, furthermore when the record is returned to later and closed, the duration of it being "open" must be worked out (total job time).

Ideally I'd like to create a form whereby I drop down a combo box and select an engineer (or type and autocomplete) it fills in the rest of his details, I then drop down another box and select a barcode (or again autocomplete) and that completes the rest of the job details. I then type in the new fields I also need, and this all gets stored in the "booking" table.

Any help greatly appreciated

I'm working with A2K3 for clarification, and can post/email my current database if required.

Thanks

 

[ReAn]

First off, what you're looking for is a JOIN operation in your query.

JOIN is a feature of SQL Language that allows you to join two tables on a field.

Eg:

SELECT Cars.*, Driver.DriverName FROM Cars JOIN Drivers ON Cars.DriverID = Drivers.ID

This would return all the colunms of Cars, and add a colunm that co-responds to the Drivername.

To learn more about JOINs i suggest this link:

http://www.w3schools.com/sql/sql_join.asp

Take a gander at that, if you're still unsure, come back and let us know.

 

[BrenOS]

...

Thanks for your quick reply, but with respect, I'm no further forward.

My problem lies that if I create a query containing the fields I need from Job and Engineer, and the couple from Booking, if I create an input form for this query it will store the values in their existing tables, whereas I wish to repeat it in the Booking table (a record of all engineers working on jobs, a lot of repeat data). I can see how the JOIN op would help me locate a record, but not help me in this instance

I think it's best if you start simple, sorry

 

[Mile-O]

I'm always curious about these projects. Do they just hand them out to anyone or do they hand them out to people who have been learning about databases already?

 

[bonekrusher]

What airline to you work for? I work for an airline and kind of know what your talking about. Email me.

 

[Lightwave]

Hi there BrenOS

It would appear that roughly speaking you've got the right structure you just don't know how to do drop downs' calculations and design forms.?

If that's the case then - start off as follows

Make sure tables are along the following lines
Engineer - unique ID and all fields only relevant to them
Job - unique barcode and all fields only relevant to them
Booking - To contain unique ID of Engineer and unique ID of job along with start time and finish time (duration can be calculated automatically). fields of engineer and job will be tied back to first two tables..

Start with three forms
Engineer form
Job form
Booking form

On the booking form combo boxes will appear automatically when you create the fields providing the booking table was created correctly. For the start time you will need a date field as well as a time and similar for the end time. You will then need to figure out calculations. Try the search command in here to see other examples of date and time calculations ...

You will come across problems as you go along which may be more specific than your initial outline at which point you should be able to use the search command here to identify solutions. One useful thing is looking at other databases that have already been built up to see how they've done things like combo boxes / calculations.
A general how do I design a whole database - is quite difficult to completly cover.!!!

 

[Gibbo]

I would suggest that you shouldn't be trying to repeat the data. The most efficient use of a database is to store the minimum data for each item in one table, using links to other tables to retrieve linked data.

Un your bookings table you would store the engineer id, the job id (or barcode) and the additional information that relates solely to the booking (eg date time). You input form can be based on a query which reads data from all 3 tables. This will allow you to input data and display the related data at the same time. However, for this to work reliably, you will need to create the links between your tables in relationships.

You could also consider using subforms. If your main booking form is based on just the enigineer id, job id and additional data for the booking, you can display a subform based on the engineer table to show the details for that engineer by linking the id in the subform to the id in the main form

 

[ScottGem]

Thanks for your quick reply, but with respect, I'm no further forward.

My problem lies that if I create a query containing the fields I need from Job and Engineer, and the couple from Booking, if I create an input form for this query it will store the values in their existing tables, whereas I wish to repeat it in the Booking table (a record of all engineers working on jobs, a lot of repeat data). I can see how the JOIN op would help me locate a record, but not help me in this instance

I think it's best if you start simple, sorry

The issue here is one of normalization. Normalization is an essential design technique for relational databases. The rules of normalization were first set down about 40 yerars ago and have not radically changed since then. The main purpose of normalization is to reduce or eliminate redundant data. Therefore a principle of normalization is that data should never exist in more than one table. So the ONLY values you need from the Job and Engineers tables in the Bookings table is the primary key (unique ID) values from the records. This is called a foreign key. Any oither info you need from these tables is pulled via joins.

If you have data entry issues you would use subforms, to display the related records.