updating parent key in child table

Hello again,

I have a series of three nested tables that I'm trying to build
relationships between. We sample pools within a site (so samples are nested
in pools and pools are nested in sites).

The database looks something like this:

tblSITE
SiteID (this is unique and is the PK)
Site info

tblPOOl
PoolPK
Site ID
PoolID (this is not unique**)
Pool info (water quality, etc)

tblSAMPLE
SamplePK
SiteID
PoolID
SampleID (this is not unique**)
PoolPK

** the PoolID and SampleID are not unique - we usually survey three pools at
each site and simply call them pools 1,2 and 3. Similarly, we take three
samples (called 1, 2 and 3) from each pool.

I have two questions:

The relationship between tblPOOL and tblSAMPLE is based on PoolPK. However,
I can't work out how to get the PoolPK into the Sample table, other than
typing it manually! This is tedious and time-consuming! I import hundreds of
rows of sample data from Excel, which includes SiteID and PoolID, and I'm not
sure how to update PoolPK.
- should I use a FORM (but then I won't be able to import bulk data)?
- should I use a QUERY to update the PoolPK? (How?)

Secondly, I have more than one sample table. We conduct analyses of
ammonium, nitrogen, chlorophyll (and more), so I actually have several sample
tables. Am I better off keeping these data in separate tables and then using
queries to join them, or trying to roll them into one big table? (If so, how?
- I can't append columns to my Sample table, so I end up copying and pasting
from Excel, and laboriously checking that my rows line up...)

Thank you for any comments that you may have about either (or both!) of my
issues.

Cheers,

buggirl

This is confusing:
- tblPool.PoolPK is unique (primary key), but tblPool.PoolID is not.
- tblSample.PoolPK identifies the pool the sample came from. Good.

Why have PoolID in tblSampleID? For any sample, you could use
tblSample.PoolPK to look up what PoolID applies (in tblPool)? By storing
PoolID as well in tblSample, you are opening the door to bad data (i.e.
where tblSample.PoolID does not match the corresponding tblPool.PoolID in
tblPool.)

Once you solve that problem, you will be able to continue on to address the
import problem.

Regarding your 2nd question, it would be better not to have seprate tables
for the different things you are testing.

Ultimately, the database structure will be very different from the flat-file
approach in Excel, so you will need something more than a copy'n'paste
approach. It will take a bit of work to design an import routine that
imports the spreadsheet into a temporary table (with the same columns as the
spreadsheet), and runs a series of checks (to ensure the data types are
right, the lookup values are valid, the required fields all have data, etc).
It will then identify any new sites and append them to your tblSite, any new
pools (appending to tblPool), and so on down through the sample data. There
will be further checks, e.g. to see if this looks like duplicate data that
has already been imported. But if you are doing this several times a day, it
will be worth the effort.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"buggirl" wrote in message
...
Hello again,

I have a series of three nested tables that I'm trying to build
relationships between. We sample pools within a site (so samples are
nested
in pools and pools are nested in sites).

The database looks something like this:

tblSITE
SiteID (this is unique and is the PK)
Site info

tblPOOl
PoolPK
Site ID
PoolID (this is not unique**)
Pool info (water quality, etc)

tblSAMPLE
SamplePK
SiteID
PoolID
SampleID (this is not unique**)
PoolPK

** the PoolID and SampleID are not unique - we usually survey three pools
at
each site and simply call them pools 1,2 and 3. Similarly, we take three
samples (called 1, 2 and 3) from each pool.

I have two questions:

The relationship between tblPOOL and tblSAMPLE is based on PoolPK.
However,
I can't work out how to get the PoolPK into the Sample table, other than
typing it manually! This is tedious and time-consuming! I import hundreds
of
rows of sample data from Excel, which includes SiteID and PoolID, and I'm
not
sure how to update PoolPK.
- should I use a FORM (but then I won't be able to import bulk data)?
- should I use a QUERY to update the PoolPK? (How?)

Secondly, I have more than one sample table. We conduct analyses of
ammonium, nitrogen, chlorophyll (and more), so I actually have several
sample
tables. Am I better off keeping these data in separate tables and then
using
queries to join them, or trying to roll them into one big table? (If so,
how?
- I can't append columns to my Sample table, so I end up copying and
pasting
from Excel, and laboriously checking that my rows line up...)

Thank you for any comments that you may have about either (or both!) of my
issues.

Cheers,

buggirl

Yes, I agree. It is confusing.

I have SiteID and PoolID in tblSample because my data in Excel needs it. I
would like to have only PoolPK (which effectively replaces SiteID and
PoolID), but I can't work out how to line up the data when I import it from
Excel.

Does that make sense?

Should I be giving the data a PoolPK in Excel, before I even import it into
Access?

I only import this data sporadically - every couple of weeks during the
summer, less frequently at other times of the year. So I'm not sure whether
it's worthwhile designing an elaborate import routine, or to stick with my
primitive cut-n-paste approach...

Thanks again for your input - I'm learning a lot!

buggirl


"Allen Browne" wrote:

This is confusing:
- tblPool.PoolPK is unique (primary key), but tblPool.PoolID is not.
- tblSample.PoolPK identifies the pool the sample came from. Good.

Why have PoolID in tblSampleID? For any sample, you could use
tblSample.PoolPK to look up what PoolID applies (in tblPool)? By storing
PoolID as well in tblSample, you are opening the door to bad data (i.e.
where tblSample.PoolID does not match the corresponding tblPool.PoolID in
tblPool.)

Once you solve that problem, you will be able to continue on to address the
import problem.

Regarding your 2nd question, it would be better not to have seprate tables
for the different things you are testing.

Ultimately, the database structure will be very different from the flat-file
approach in Excel, so you will need something more than a copy'n'paste
approach. It will take a bit of work to design an import routine that
imports the spreadsheet into a temporary table (with the same columns as the
spreadsheet), and runs a series of checks (to ensure the data types are
right, the lookup values are valid, the required fields all have data, etc).
It will then identify any new sites and append them to your tblSite, any new
pools (appending to tblPool), and so on down through the sample data. There
will be further checks, e.g. to see if this looks like duplicate data that
has already been imported. But if you are doing this several times a day, it
will be worth the effort.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"buggirl" wrote in message
...
Hello again,

I have a series of three nested tables that I'm trying to build
relationships between. We sample pools within a site (so samples are
nested
in pools and pools are nested in sites).

The database looks something like this:

tblSITE
SiteID (this is unique and is the PK)
Site info

tblPOOl
PoolPK
Site ID
PoolID (this is not unique**)
Pool info (water quality, etc)

tblSAMPLE
SamplePK
SiteID
PoolID
SampleID (this is not unique**)
PoolPK

** the PoolID and SampleID are not unique - we usually survey three pools
at
each site and simply call them pools 1,2 and 3. Similarly, we take three
samples (called 1, 2 and 3) from each pool.

I have two questions:

The relationship between tblPOOL and tblSAMPLE is based on PoolPK.
However,
I can't work out how to get the PoolPK into the Sample table, other than
typing it manually! This is tedious and time-consuming! I import hundreds
of
rows of sample data from Excel, which includes SiteID and PoolID, and I'm
not
sure how to update PoolPK.
- should I use a FORM (but then I won't be able to import bulk data)?
- should I use a QUERY to update the PoolPK? (How?)

Secondly, I have more than one sample table. We conduct analyses of
ammonium, nitrogen, chlorophyll (and more), so I actually have several
sample
tables. Am I better off keeping these data in separate tables and then
using
queries to join them, or trying to roll them into one big table? (If so,
how?
- I can't append columns to my Sample table, so I end up copying and
pasting
from Excel, and laboriously checking that my rows line up...)

Thank you for any comments that you may have about either (or both!) of my
issues.

Cheers,

buggirl

Build a table in Access that has the exact same columns as Excel, so you can
cut'n'paste into that one.

You can then connect it in a query to lookup the PoolPK value for the value
(assuming there is some way to determine this), and just use Append queries
to populate the real tables from the temporary one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"buggirl" wrote in message
...
Yes, I agree. It is confusing.

I have SiteID and PoolID in tblSample because my data in Excel needs it. I
would like to have only PoolPK (which effectively replaces SiteID and
PoolID), but I can't work out how to line up the data when I import it
from
Excel.

Does that make sense?

Should I be giving the data a PoolPK in Excel, before I even import it
into
Access?

I only import this data sporadically - every couple of weeks during the
summer, less frequently at other times of the year. So I'm not sure
whether
it's worthwhile designing an elaborate import routine, or to stick with my
primitive cut-n-paste approach...

Thanks again for your input - I'm learning a lot!

buggirl


"Allen Browne" wrote:

This is confusing:
- tblPool.PoolPK is unique (primary key), but tblPool.PoolID is not.
- tblSample.PoolPK identifies the pool the sample came from. Good.

Why have PoolID in tblSampleID? For any sample, you could use
tblSample.PoolPK to look up what PoolID applies (in tblPool)? By storing
PoolID as well in tblSample, you are opening the door to bad data (i.e.
where tblSample.PoolID does not match the corresponding tblPool.PoolID in
tblPool.)

Once you solve that problem, you will be able to continue on to address
the
import problem.

Regarding your 2nd question, it would be better not to have seprate
tables
for the different things you are testing.

Ultimately, the database structure will be very different from the
flat-file
approach in Excel, so you will need something more than a copy'n'paste
approach. It will take a bit of work to design an import routine that
imports the spreadsheet into a temporary table (with the same columns as
the
spreadsheet), and runs a series of checks (to ensure the data types are
right, the lookup values are valid, the required fields all have data,
etc).
It will then identify any new sites and append them to your tblSite, any
new
pools (appending to tblPool), and so on down through the sample data.
There
will be further checks, e.g. to see if this looks like duplicate data
that
has already been imported. But if you are doing this several times a day,
it
will be worth the effort.

"buggirl" wrote in message
...
Hello again,

I have a series of three nested tables that I'm trying to build
relationships between. We sample pools within a site (so samples are
nested
in pools and pools are nested in sites).

The database looks something like this:

tblSITE
SiteID (this is unique and is the PK)
Site info

tblPOOl
PoolPK
Site ID
PoolID (this is not unique**)
Pool info (water quality, etc)

tblSAMPLE
SamplePK
SiteID
PoolID
SampleID (this is not unique**)
PoolPK

** the PoolID and SampleID are not unique - we usually survey three
pools
at
each site and simply call them pools 1,2 and 3. Similarly, we take
three
samples (called 1, 2 and 3) from each pool.

I have two questions:

The relationship between tblPOOL and tblSAMPLE is based on PoolPK.
However,
I can't work out how to get the PoolPK into the Sample table, other
than
typing it manually! This is tedious and time-consuming! I import
hundreds
of
rows of sample data from Excel, which includes SiteID and PoolID, and
I'm
not
sure how to update PoolPK.
- should I use a FORM (but then I won't be able to import bulk data)?
- should I use a QUERY to update the PoolPK? (How?)

Secondly, I have more than one sample table. We conduct analyses of
ammonium, nitrogen, chlorophyll (and more), so I actually have several
sample
tables. Am I better off keeping these data in separate tables and then
using
queries to join them, or trying to roll them into one big table? (If
so,
how?
- I can't append columns to my Sample table, so I end up copying and
pasting
from Excel, and laboriously checking that my rows line up...)

Thank you for any comments that you may have about either (or both!) of
my
issues.