forms with data from multiple tables

  • Thread starter Thread starter buggirl
  • Start date Start date
B

buggirl

Hi,

I am trying to build two tables containing information about fish that we
have collected:

tbl Fish:
FishPK (one for each individual fish; autonumber)
CollectionPK (links to tbl Collection which contains information about where
and when we collected the fish)
FishName
FishAge
FishSex
FishLength

tbl FishAnalysis:
FishPK
Analysis1
Analysis2
Analysis3
Analysis4

The chemical analyses are only performed on a subset of the entire fish
collection.

I am pretty happy with this design, but am open to suggestions! In
particular, I want to know if this design allows me to build a form for
entering results of the analyses - when I receive the data, it looks like this
Collection ID (not PK)
FishName
Analysis1
Analysis2
Analysis3
Analysis4
- which means that I have to collect data from two separate tables before
adding the new results to tbl FishAnalysis. How do I build a form to simplify
this process? And is my design correct to enable my form?

I would appreciate any input!

Thanks,

buggirl
 
Try this --
SELECT [Collection ID], Fish.FishName, Analysis1, Analysis2, Analysis3,
Analysis4
FROM Fish LEFT JOIN FishAnalysis ON Fish.FishPK = FishAnalysis.FishPK;
 
Thanks Karl,

that looks as though it could work, but to be honest I have no idea how to
implement that. Can you walk me through it step-by-step (I'm a biologist, not
a Access expert!).

cheers,

buggirl

KARL DEWEY said:
Try this --
SELECT [Collection ID], Fish.FishName, Analysis1, Analysis2, Analysis3,
Analysis4
FROM Fish LEFT JOIN FishAnalysis ON Fish.FishPK = FishAnalysis.FishPK;

--
Build a little, test a little.


buggirl said:
Hi,

I am trying to build two tables containing information about fish that we
have collected:

tbl Fish:
FishPK (one for each individual fish; autonumber)
CollectionPK (links to tbl Collection which contains information about where
and when we collected the fish)
FishName
FishAge
FishSex
FishLength

tbl FishAnalysis:
FishPK
Analysis1
Analysis2
Analysis3
Analysis4

The chemical analyses are only performed on a subset of the entire fish
collection.

I am pretty happy with this design, but am open to suggestions! In
particular, I want to know if this design allows me to build a form for
entering results of the analyses - when I receive the data, it looks like this
Collection ID (not PK)
FishName
Analysis1
Analysis2
Analysis3
Analysis4
- which means that I have to collect data from two separate tables before
adding the new results to tbl FishAnalysis. How do I build a form to simplify
this process? And is my design correct to enable my form?

I would appreciate any input!

Thanks,

buggirl
Click to expand...
Click to expand...
 
Hi

First of all table design. It appears that your tblFishAnalysis has 4
different analysis fields
A better design would be to have that table contain the FishPK and one
analysis field
This table would have 4 records for each fish

Now mak a query that links the collection and fish tables on the
collectionPK field This query will have the fish information and collection
information together
Make this the source for your Main form. You will be able to page through
all of your fish.

Make a subform on that form based on the fishanalysis table. link by FishPK

For each page of your form you will see whatever fish and collection
information and the subform will list the analasys information
 
What I posted was the SQL for a query.

You can use it by creating a new query in dewign view, clicking on VIEW -
SQL View, pasting the post in the window that opens, and save.

Or you can create a query in design view, placing both tables in the space
above the grid. Click on FishPK field in the Fish table and drag to the
FishPK field of FishAnalysis table. Click on the resulting connecting line
and then double click it. Select the option that say to show all records
from Fish and only those from FishAnalysis that match.

Drag down fields 'Collection ID' and 'FishName' from table Fish. Drag down
fields Analysis1, Analysis2, Analysis3,
and Analysis4 from table FishAnalysis.

Save.

--
Build a little, test a little.


buggirl said:
Thanks Karl,

that looks as though it could work, but to be honest I have no idea how to
implement that. Can you walk me through it step-by-step (I'm a biologist, not
a Access expert!).

cheers,

buggirl

KARL DEWEY said:
Try this --
SELECT [Collection ID], Fish.FishName, Analysis1, Analysis2, Analysis3,
Analysis4
FROM Fish LEFT JOIN FishAnalysis ON Fish.FishPK = FishAnalysis.FishPK;

--
Build a little, test a little.


buggirl said:
Hi,

I am trying to build two tables containing information about fish that we
have collected:

tbl Fish:
FishPK (one for each individual fish; autonumber)
CollectionPK (links to tbl Collection which contains information about where
and when we collected the fish)
FishName
FishAge
FishSex
FishLength

tbl FishAnalysis:
FishPK
Analysis1
Analysis2
Analysis3
Analysis4

The chemical analyses are only performed on a subset of the entire fish
collection.

I am pretty happy with this design, but am open to suggestions! In
particular, I want to know if this design allows me to build a form for
entering results of the analyses - when I receive the data, it looks like this
Collection ID (not PK)
FishName
Analysis1
Analysis2
Analysis3
Analysis4
- which means that I have to collect data from two separate tables before
adding the new results to tbl FishAnalysis. How do I build a form to simplify
this process? And is my design correct to enable my form?

I would appreciate any input!

Thanks,

buggirl
Click to expand...
Click to expand...
Click to expand...
 
Back
Top