Need help with joins and queries

[jdcollins88]

Hello, I am very new to access and am having trouble getting started. I have acquired an already constructed access database and am trying to pull out data in a specific format so I can run some stats. I have multiple tables, two of which contain the information I am after. One of the tables contain the capture information including which net the animal was captured in, which site, date etc. Another table contains the information on said net for that site. For example

Table 1.

Species	Date	Site	Net
MYLU	8/14/2010	FR143	A
MYSE	8/16/2011	FR143	A
MYSO	8/16/2011	FR142	A

<tbody>
</tbody>

Table 2.

Site	Date	Net A Height	Net B Height
FR143	8/14/2010	2.6	3.5
FR143	8/16/2010	5.2	2.6

<tbody>
</tbody>

Currently only site name is joined between the tables, I believe I should join date as well, but am a little confused as how to join the net to net height, instead of trying to explain here is my desired outcome.

Site	Date	Net	Height of net	Number captured
FR143	8/14/2010	A	2.6	1
FR143	8/16/2011	A	5.2	2

<tbody>
</tbody>

I've tried multiple ways of querying without any luck so here I am.
If further clarification is needed I will be more than happy to provide it

Thanks for the help
Jason

 

[alansidman]

Here is a SQL statement to capture the data you are showing.

SELECT Table1.Site, Table1.Date1, Table1.Net, Table2.NetAHeight
FROM Table1 INNER JOIN Table2 ON (Table1.Date1 = Table2.Date1) AND (Table1.Site = Table2.Site);

Note I changed the Date Field to Date1 as Date is a reserved name in Access and may cause issues in the future.

 

[jdcollins88]

Thanks that is what I needed