Cannot view form in form view

SELECT tblCollectionData.SpeciesCode, tblCollectionData.[Survey Day],
tblCollectionData.[Survey Month], tblCollectionData.[Survey Year],
tblCollectionData.Site, tblCollectionData.Subsite, tblCollectionData.[Total
Count], tblCollectionData.Males, tblCollectionData.Females,
tblCollectionData.
[Collection Method], tblCollectionData.[Life Stage], tblCollectionData.[Host
Plant], tblCollectionData.Source, tblCollectionData.Collector,
tblCollectionData.[Determined By], tblCollectionData.[Known Exotic?],
tblCollectionData.Notes, tblSiteInformation.County, tblSiteInformation.Quad,
tblSiteInformation.PhysProv, tblSiteInformation.Habitat, tblSiteInformation.
Latitude, tblSiteInformation.Longitude, tblSiteInformation.Directions
FROM tblSiteInformation INNER JOIN tblCollectionData ON tblSiteInformation.
Site = tblCollectionData.Site;


Change INNER to LEFT if you want to see everything from tblCollectionData
Change INNER to RIGHT if you want to see everything from tblSiteInformation


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"JenS via AccessMonster.com" u37349@uwe wrote in message
news:78364ed7a718f@uwe...
Thanks Gina and I'm sorry for my delay in replying... I did as you
suggested
and this is what I ended up with:

SELECT tblCollectionData.SpeciesCode, tblCollectionData.[Survey Day],
tblCollectionData.[Survey Month], tblCollectionData.[Survey Year],
tblCollectionData.Site, tblCollectionData.Subsite,
tblCollectionData.[Total
Count], tblCollectionData.Males, tblCollectionData.Females,
tblCollectionData.
[Collection Method], tblCollectionData.[Life Stage],
tblCollectionData.[Host
Plant], tblCollectionData.Source, tblCollectionData.Collector,
tblCollectionData.[Determined By], tblCollectionData.[Known Exotic?],
tblCollectionData.Notes, tblSiteInformation.County,
tblSiteInformation.Quad,
tblSiteInformation.PhysProv, tblSiteInformation.Habitat,
tblSiteInformation.
Latitude, tblSiteInformation.Longitude, tblSiteInformation.Directions
FROM tblSiteInformation INNER JOIN tblCollectionData ON
tblSiteInformation.
Site = tblCollectionData.Site;

Does this help?

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Actually, since this is a new database, there hasn't been any data entered
yet in either of those tables. It is the same field - both tables have a
site and a subsite, but these will contain the same information. I was
advised it the SiteInformation table to do a double primary key for those two
fields, and they would be related to the site and subsite foreign keys in the
collection data table. Does that make sense the way I am explaining it?

Do I need to have some data entered before I start this form? I was thinking
that even though this form is more complex than others I have used that are
based on a single table, that I would just enter all the data for those two
tables using the form and that it would then just transfer that information
to those tables.

Jen

Gina Whipp wrote:
In both tables there is a field called Site. Do they both contain data? Or
does one contain data and the other not? And are these two tables related
in any way PrimaryKey/ForeignKey, etc...

Thanks Gina and I'm sorry for my delay in replying... I did as you
suggested
[quoted text clipped - 22 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Jen

tblSiteInformation a PrimaryKey, not sure why you were told to have a two
PK's but you need one the is indexed (No Duplicates), then tblCollectionData
needs the ForeignKey SiteID (no need to index). Then in the relationship
window drop the two tables in and create the link.

Why do you need to do this because I fear the way you have it set up you
will not be able to enter information in the form the way they are joined
together. Also note you shouldn't use this one form to enter information
for both tables. tblCollectionData will not let you enter any information
in the Site Field that is not already in the tblSiteInformation table.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"JenS via AccessMonster.com" u37349@uwe wrote in message
news:7837041b81ffd@uwe...
Actually, since this is a new database, there hasn't been any data entered
yet in either of those tables. It is the same field - both tables have a
site and a subsite, but these will contain the same information. I was
advised it the SiteInformation table to do a double primary key for those
two
fields, and they would be related to the site and subsite foreign keys in
the
collection data table. Does that make sense the way I am explaining it?

Do I need to have some data entered before I start this form? I was
thinking
that even though this form is more complex than others I have used that
are
based on a single table, that I would just enter all the data for those
two
tables using the form and that it would then just transfer that
information
to those tables.

Jen

Gina Whipp wrote:
In both tables there is a field called Site. Do they both contain data?
Or
does one contain data and the other not? And are these two tables related
in any way PrimaryKey/ForeignKey, etc...

Thanks Gina and I'm sorry for my delay in replying... I did as you
suggested
[quoted text clipped - 22 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

I was trying to follow the rule of having several small tables but do you
think in this case I should combine the siteinformation and collection data
tables? I am using almost every field in both those tables on the form
anyway.
Jen



Gina Whipp wrote:
Jen

tblSiteInformation a PrimaryKey, not sure why you were told to have a two
PK's but you need one the is indexed (No Duplicates), then tblCollectionData
needs the ForeignKey SiteID (no need to index). Then in the relationship
window drop the two tables in and create the link.

Why do you need to do this because I fear the way you have it set up you
will not be able to enter information in the form the way they are joined
together. Also note you shouldn't use this one form to enter information
for both tables. tblCollectionData will not let you enter any information
in the Site Field that is not already in the tblSiteInformation table.

Actually, since this is a new database, there hasn't been any data entered
yet in either of those tables. It is the same field - both tables have a
[quoted text clipped - 27 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Jen,

Unless every piece of data has different Site Information then the answer is
no. You have set it up correctly. (Also note, it's not small tables it's
relational design.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

"JenS via AccessMonster.com" u37349@uwe wrote in message
news:7837cceadff2f@uwe...
I was trying to follow the rule of having several small tables but do you
think in this case I should combine the siteinformation and collection
data
tables? I am using almost every field in both those tables on the form
anyway.
Jen



Gina Whipp wrote:
Jen

tblSiteInformation a PrimaryKey, not sure why you were told to have a two
PK's but you need one the is indexed (No Duplicates), then
tblCollectionData
needs the ForeignKey SiteID (no need to index). Then in the relationship
window drop the two tables in and create the link.

Why do you need to do this because I fear the way you have it set up you
will not be able to enter information in the form the way they are joined
together. Also note you shouldn't use this one form to enter information
for both tables. tblCollectionData will not let you enter any information
in the Site Field that is not already in the tblSiteInformation table.

Actually, since this is a new database, there hasn't been any data
entered
yet in either of those tables. It is the same field - both tables have
a
[quoted text clipped - 27 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Hi Gina,

I am going to have to send you flowers once we are done with this...

ok, so since every piece of data will not have different site information,
the person doing the data entry will esentially have to fill out 2 forms?
One for collection data and another for site information? And they would
have to fill out the site information form first because otherwise they can't
include the site in the collection data form?

That's ok it just seems like a pain to have to deal with 2 forms every time
they have a new specimen to deal with. Is this a case where I should use a
subform?

Gina Whipp wrote:
Jen,

Unless every piece of data has different Site Information then the answer is
no. You have set it up correctly. (Also note, it's not small tables it's
relational design.)

I was trying to follow the rule of having several small tables but do you
think in this case I should combine the siteinformation and collection
[quoted text clipped - 24 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Jen,

Actually, once you enter the site information once then whenever the SiteID
is entered the information on the collection side should automatically show
up. (Provided the relationships are set up properly).

If it were me, I would have a seperate form to enter the Site Information.
Then when I enter the Collection Data and pick a SiteID all is done for me.
A button to open the form to enter the Site Information (which can be placed
on the form where the Collection Data is entered) should suffice.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"JenS via AccessMonster.com" u37349@uwe wrote in message
news:7837fb4c44e09@uwe...
Hi Gina,

I am going to have to send you flowers once we are done with this...

ok, so since every piece of data will not have different site information,
the person doing the data entry will esentially have to fill out 2 forms?
One for collection data and another for site information? And they would
have to fill out the site information form first because otherwise they
can't
include the site in the collection data form?

That's ok it just seems like a pain to have to deal with 2 forms every
time
they have a new specimen to deal with. Is this a case where I should use
a
subform?

Gina Whipp wrote:
Jen,

Unless every piece of data has different Site Information then the answer
is
no. You have set it up correctly. (Also note, it's not small tables it's
relational design.)

I was trying to follow the rule of having several small tables but do you
think in this case I should combine the siteinformation and collection
[quoted text clipped - 24 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Oh, no flowers neccessary... just glad to give back what has been given to
me!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"JenS via AccessMonster.com" u37349@uwe wrote in message
news:7837fb4c44e09@uwe...
Hi Gina,

I am going to have to send you flowers once we are done with this...

ok, so since every piece of data will not have different site information,
the person doing the data entry will esentially have to fill out 2 forms?
One for collection data and another for site information? And they would
have to fill out the site information form first because otherwise they
can't
include the site in the collection data form?

That's ok it just seems like a pain to have to deal with 2 forms every
time
they have a new specimen to deal with. Is this a case where I should use
a
subform?

Gina Whipp wrote:
Jen,

Unless every piece of data has different Site Information then the answer
is
no. You have set it up correctly. (Also note, it's not small tables it's
relational design.)

I was trying to follow the rule of having several small tables but do you
think in this case I should combine the siteinformation and collection
[quoted text clipped - 24 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Hi Gina! I spent some time over the weekend messing around with this and I
think I got it. I liked your idea about entering the data on 2 seperate
tables/forms, but the problem was that I already had that going on - the data
entry person had to first enter all the species on a species table, then deal
with the form housing information on collection data and site information.
What I decided to do was create a subform for site information on the
collection data form. I entered some dummy data and it seems to be working!
Everything that gets entered shows up on both tables, and the drop down menus
get updated correctly, etc. I need to really mess with it more to work out
the kinks but I think the basic idea is sound. That also enabled me to get
rid of the double primary key jazz, which I admit I was uncomfortable about
even though the instructor for my access class assurred me that it would work.


Anyway, I will post a small note to you in a week or two after I've had a
chance to start entering some real data and really try it out, but wanted to
thank you so much for all of your help! This dialouge really helped point
out some basic things I needed to doi (and shouldn't do) and really helped me
understand the forms process a lot better. Thanks so much!

Jen

Gina Whipp wrote:
Oh, no flowers neccessary... just glad to give back what has been given to
me!

Hi Gina,

[quoted text clipped - 25 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Your most welcome!

And THANK YOU for posting back!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"JenS via AccessMonster.com" u37349@uwe wrote in message
news:786ad5a6fdade@uwe...
Hi Gina! I spent some time over the weekend messing around with this and
I
think I got it. I liked your idea about entering the data on 2 seperate
tables/forms, but the problem was that I already had that going on - the
data
entry person had to first enter all the species on a species table, then
deal
with the form housing information on collection data and site information.
What I decided to do was create a subform for site information on the
collection data form. I entered some dummy data and it seems to be
working!
Everything that gets entered shows up on both tables, and the drop down
menus
get updated correctly, etc. I need to really mess with it more to work
out
the kinks but I think the basic idea is sound. That also enabled me to
get
rid of the double primary key jazz, which I admit I was uncomfortable
about
even though the instructor for my access class assurred me that it would
work.


Anyway, I will post a small note to you in a week or two after I've had a
chance to start entering some real data and really try it out, but wanted
to
thank you so much for all of your help! This dialouge really helped point
out some basic things I needed to doi (and shouldn't do) and really helped
me
understand the forms process a lot better. Thanks so much!

Jen

Gina Whipp wrote:
Oh, no flowers neccessary... just glad to give back what has been given
to
me!

Hi Gina,

[quoted text clipped - 25 lines]

Jen

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1