If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
forms with data from multiple tables
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 |
#2
|
|||
|
|||
forms with data from multiple tables
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" wrote: 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 |
#3
|
|||
|
|||
forms with data from multiple tables
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" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
forms with data from multiple tables
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 "buggirl" wrote in message ... 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 |
#5
|
|||
|
|||
forms with data from multiple tables
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" wrote: 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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|