Setting up continuous fields

[Sarrina]

I am trying to figure out how to set up continous fields
in a new database that I am creating. What I mean is
(example of what I need, not the information I am
inputting)


Field 1 = Owner
Field 2 = Residence
Field 3 = Vehicle 1's Date of Purchase
Vehicle 2's Date of Purchase
Vehicle 3's Date of Purchase
Vehicle 4's Date of Purchase
Vehicle 5's Date of Purchase
Field 4 = Vehicle 1
Vehicle 2
Vehicle 3
Vehicle 4
Vehicle 5
Field 5 = Vehicle 1's HP
Vehicle 2's HP
Vehicle 3's HP
Vehicle 4's HP
Vehicle 5's HP
Field 6 = Vehicle 1's Color
Vehicle 2's Color
Vehicle 3's Color
Vehicle 4's Color
Vehicle 5's Color


As you can see, I want to have continuous cells in field's
3-6. They are all in relationship to Field 1. But I
don't want to have numerous fields as some of Field's 3-6
would be different. One owner may have 3 vehicles whereas
another owner may have 20.

How in the world do I make Access do this dirty work?

Thanks in advance!
Sarr

 

[Michel Walsh]

Hi,

You make two tables. The first one is made of fields 1 and 2 only (and what
one owner owns once). Call the table owners.

The second table is made of fields 1, 3, 4, 5, .... and what belong to each
vehicle. Consider that a given vehicle "owns" one "owner". Call the table
vehicles.


For one owner have as many ROWS in vehicles as required.


That's all.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

Thanks for your response. I think I confused myself, and
not enough detail on that example, so let me outline it
better with the actual data.

The basic information I'm trying to draw out are:

Field 1: Claim # (PK)
Field 2: Monitoring Well #
Field 3: Date of Sampling
Field 4: Benzene Results
Field 5: MTBE Results
Field 6: BTEX Results
Field 7: TPH Results

There will be multiple information for fields 2 - 7.
Example of what a report should look like after
information is entered into Access:

Claim 40050
Monitoring Well #1
Date of Sampling: 01/01/01
Benzene: 100.3
MTBE: 80.6
BTEX: 1135.7
TPH: 456.3
Date of Sampling: 03/01/01
Benzene: 99.3
MTBE: 77.6
BTEX: 1102.7
TPH: 568.3
Date of Sampling: 07/15/01
Benzene: 156.3
MTBE: 64.6
BTEX: 999.7
TPH: 357.3
Monitoring Well #2
etc.
Monitoring Well #3
etc.


Each claim would display a different number of monitoring
wells. For each well, there will be different number of
dates where samples were taken. As you can assume - it
may dramatically vary on number of wells/dates. We have
claims that have up to 30 monitoring wells that were
sampled monthly for a period of 3 years. Other claims may
have under 5, or 5-10, etc, monitoring wells that were
only sampled quarterly for a period of 2 years (many
variables). All this information needs to be entered.

I need to create a form for someone to enter the
information. Afterwards, I will need to run a report that
looks similiar to the above, along with graphs after
calculations are made.

I've been playing with this for the past week. Field 1 is
already in a table with other basic information, along
with other tables. The problem is setting up another
table (or form?) that would allow multiple information in
the same field without actually manually going through and
entering numerous rows/columns? for this.

Make any/more sense?

Thanks,
Sarr

 

[Tim Ferguson]

How in the world do I make Access do this dirty work?

Assuming that a vehicle only has one owner, then you need one table for
Owners and one table for Vehicles. If owners can share the same vehicle,
then you need another table for Ownership.

For a working example of a one-to-many relationship, see any book on
database design, or the help files, or look round the Northwind sample.

Best wishes


Tim F

 

[Michel Walsh]

Hi,



A possible design would be

Samplings
SampleNumber, Well, DateTaken Benzene, MTBE, BTEX, TPH ' fields
1 1 xxxxxx 100.3 80.6 1135.7
456.3
.... ' data


It is then a matter to link the samplings, to the claim

Claims
ClaimNumber, SampleNumber
1 1
1 2
1 3
....


In a form, you can display the whole thing with a main-form (Claims)
sub-form ( Samplings) design.

If you think other measurements can be added in the future, may be you could
remove the Benzene, MTBE, BTEX, .... fields from Samplings, and make another
table, measurements:


Measurements
SampleNumber, Product, Measurement
1 Benzene 100.3
1 MTBE 80.6
1 BTEX 1135.7
....



and, since the field name is independent of the object of the measurement,
you can add Product= whatever, if you ever measure it! On the other hand, it
give you another level of form-subform-subsubform, which may soon appear to
be too complex...



Hoping it may help,
Vanderghast, Access MVP

 

[Sarrina]

Thanks again.

I will take a look at this route and see what I can come
up with.

Your input helps.

Thanks!
Sarr

 

[Sarrina]

Tim,

Printed out the information - and also gonna view Michel's
suggestion.

Hopefully now I can finally get this thing to work.

Thanks!
Sarr