Missing Entries

Hi,
I have a palms table that stores palm census for each and every palm
for a block on every trial.
Every trial has a set number of blocks and set number of palms.
Each of the palms has a palm census done every year to check its
status.

The problem firstly is that for data entry: Entry is done such that
the trial, plot is entered, and also palm number and the status.
Sometimes not all the palms are being entered, so there are a few
missing palms that have no status in a year and then in the next year
it is entered.


I would like to do a query that picks up those palms that have not
been entered. Firstly there is nowhere in the db that states the
total
no of palms for each plot - there is only total no of plots for the
trial.


i have decided to change the data entry form such that the palm
number
is not entered but should automatically show, so that a palm is not
missed.


i need help to how to pick up from the current data which palms are
missing.


Great appreciation for all assistance given,


Thanks

Re: Missing Entries

This seems to be "our day for cryptic questions." Without some specific
details, it's going to be difficult to help you. Of what information does a
"palm census" consist. Are these "palms" as in palm trees, or "palms" as in
the inside of a closed hand? Do you me "trials" as in "trying something
out" or do you mean "trails" as in "pathways". How does one determine the
number of a specific palm? How is the data stored -- you say you have a
palms table, and would it be feasible to tell us what information is kept in
that table? Is there a separate record in the palms table for each year's
status entry? Do you try to keep multiple year's status on a palm in a
single record? How do you relate a palm to the block in which it resides --
is there a separate, related block table (and a foreign key to the block in
the palm table)?

Larry Linson
Microsoft Access MVP

"c8tz" wrote in message
news:1193355763.989167.13570@i13g2000prf.googlegroups.com...
> Hi,
> I have a palms table that stores palm census for each and every palm
> for a block on every trial.
> Every trial has a set number of blocks and set number of palms.
> Each of the palms has a palm census done every year to check its
> status.
>
> The problem firstly is that for data entry: Entry is done such that
> the trial, plot is entered, and also palm number and the status.
> Sometimes not all the palms are being entered, so there are a few
> missing palms that have no status in a year and then in the next year
> it is entered.
>
>
> I would like to do a query that picks up those palms that have not
> been entered. Firstly there is nowhere in the db that states the
> total
> no of palms for each plot - there is only total no of plots for the
> trial.
>
>
> i have decided to change the data entry form such that the palm
> number
> is not entered but should automatically show, so that a palm is not
> missed.
>
>
> i need help to how to pick up from the current data which palms are
> missing.
>
>
> Great appreciation for all assistance given,
>
>
> Thanks
>

Re: Missing Entries

On Oct 26, 11:28 am, "Larry Linson" wrote:
> This seems to be "our day for cryptic questions." Without some specific
> details, it's going to be difficult to help you. Of what information does a
> "palm census" consist. Are these "palms" as in palm trees, or "palms" as in
> the inside of a closed hand? Do you me "trials" as in "trying something
> out" or do you mean "trails" as in "pathways". How does one determine the
> number of a specific palm? How is the data stored -- you say you have a
> palms table, and would it be feasible to tell us what information is kept in
> that table? Is there a separate record in the palms table for each year's
> status entry? Do you try to keep multiple year's status on a palm in a
> single record? How do you relate a palm to the block in which it resides --
> is there a separate, related block table (and a foreign key to the block in
> the palm table)?
>
> Larry Linson
> Microsoft Access MVP
>
> "c8tz" wrote in message
>
> news:1193355763.989167.13570@i13g2000prf.googlegroups.com...
>
>
>
> > Hi,
> > I have a palms table that stores palm census for each and every palm
> > for a block on every trial.
> > Every trial has a set number of blocks and set number of palms.
> > Each of the palms has a palm census done every year to check its
> > status.
>
> > The problem firstly is that for data entry: Entry is done such that
> > the trial, plot is entered, and also palm number and the status.
> > Sometimes not all the palms are being entered, so there are a few
> > missing palms that have no status in a year and then in the next year
> > it is entered.
>
> > I would like to do a query that picks up those palms that have not
> > been entered. Firstly there is nowhere in the db that states the
> > total
> > no of palms for each plot - there is only total no of plots for the
> > trial.
>
> > i have decided to change the data entry form such that the palm
> > number
> > is not entered but should automatically show, so that a palm is not
> > missed.
>
> > i need help to how to pick up from the current data which palms are
> > missing.
>
> > Great appreciation for all assistance given,
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

hi thanks for the reply.

ok. Palm is a tree which is part of a block which is in an
experiment trial.
There are set number of palms in a block, and set number of blocks in
a trial. ie. 16 palms in a block - there are 48 blocks in a trial.
Every year a palm census is done for each palm where the recorder
checks the palm and puts down whether healthy, missing, dead, sterile
etc.
Each Palm is given a number - Palm1, palm 2 etc. and this same palm is
monitored every year.
The data entry uses the form to put in the palm number and its status.
There are times that a census hasn't been done for a palm, thus that
Palm is not entered for the year or it could be that the data clerk
missed that palm.

There are two tables that is Palms and PalmCensus. Palms table has
the data for all the palm census that has been done throughout since,
whilst PalmCensus table only shows the last or latest census that was
done.

I want to be able to go through and filter out the missing palms -
palms that have not been recorded-

I hope i'm clear.

Re: Missing Entries

On Oct 25, 9:38 pm, vulnerableb...@gmail.com wrote:

>
> hi thanks for the reply.
>
> ok. Palm is a tree which is part of a block which is in an
> experiment trial.
> There are set number of palms in a block, and set number of blocks in
> a trial. ie. 16 palms in a block - there are 48 blocks in a trial.
> Every year a palm census is done for each palm where the recorder
> checks the palm and puts down whether healthy, missing, dead, sterile
> etc.
> Each Palm is given a number - Palm1, palm 2 etc. and this same palm is
> monitored every year.
> The data entry uses the form to put in the palm number and its status.
> There are times that a census hasn't been done for a palm, thus that
> Palm is not entered for the year or it could be that the data clerk
> missed that palm.
>
> There are two tables that is Palms and PalmCensus. Palms table has
> the data for all the palm census that has been done throughout since,
> whilst PalmCensus table only shows the last or latest census that was
> done.
>
> I want to be able to go through and filter out the missing palms -
> palms that have not been recorded-
>
> I hope i'm clear

I hope Larry won't mind me jumping in on this one.

Suppose I have the following schema and data:

tblPalms
PID AutoNumber (Primary key)
PalmTag Text
PalmLocation Text
PID PalmTag PalmLocation
1 A34B5 (233.2, 45.1) Lot 1
2 M38G19 (165.2, 22.3) Lot 6
3 G11R15 (75.3, 122.3) Lot 15
4 L82K78 (92.3, 98.7) Lot 9
5 Z28R92 (426.8, 92.3) Lot 18
6 K84D12 (81.5, 42.2) Lot 29
7 K23G23 (16.7, 37.9) Lot 9
8 I18M32 (448.3, 233.7) Lot 11
9 E93D02 (383.2, 67.3) Lot 6
10 W92A40 (83.8, 92.3) Lot 8
11 R72I39 (45.9, 129.4) Lot 3

tblBlocks
BID AutoNumber (Primary key)
BlockName Text
BlockLocation Text
BID BlockName BlockLocation
1 B17 Main to East Ave. between Fifth and Sixth (I used city
blocks :-))
2 B18 Main to East Ave. between Sixth and Seventh
3 B20 Main to West Ave. between Fifth and Sixth
4 B27 Elm to Maple between Eighth and Ninth

tblTrials
TID AutoNumber (Primary key)
TrialName Text
TID TrialName
1 Salt effect
2 Moisture effect

tblTrialBlocks
TBID AutoNumber (Primary key)
TID Long (Foreign Key)
BID Long (Foreign Key)
TBID TID BID
1 1 1
2 1 2
3 1 3
4 2 4
5 2 5

tblBlockPalms
BPID AutoNumber (Primary key)
BID Long (Foreign Key)
PID Long (Foreign Key)
BPID BID PID
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 6
7 2 7
8 2 8
9 2 9
10 2 10
11 2 11

tblCensusData
CDID AutoNumber (Primary key)
PID Long (Foreign Key)
PalmStatus Long (Foreign Key)
theYear Long
theBlock Long
theTrial Long
CDID PID PalmStatus theYear theBlock theTrial
1 1 3 2006 1 1
2 2 4 2006 1 1
3 3 3 2006 1 1
4 4 4 2006 1 1
5 6 4 2006 2 1
6 7 4 2006 2 1
....
88 1 4 2007 1 1
89 2 4 2007 1 1
90 3 4 2007 1 1
91 4 5 2007 1 1
92 5 5 2007 1 1 <-- this one was missed in 2006
93 6 4 2007 2 1
94 7 5 2007 2 1

tblPalmStatus
PSID AutoNumber (Primary key)
PalmStatus Text
PSID PalmStatus
1 Excellent
2 Great
3 Good
4 Well
5 Fair
6 Poor
7 Sterile
8 Dead
9 Missing

That way you can get all the blocks in a trial and all the palms in a
block. This would even allow for a palm to move from one block to
another.

Obtain the cross product set by combining each PID with all years used
so far in a self join and letting the UNION SELECT filter out
duplicates:

qryAllPalmYears (14 records):
SELECT CStr(tblCensusData.PID & "_" & tblCensusData.theYear) As
PID_Year FROM tblCensusData, tblCensusData AS tblCensusData_1 UNION
SELECT CStr(tblCensusData.PID & "_" & tblCensusData_1.theYear) As
PID_Year FROM tblCensusData, tblCensusData AS tblCensusData_1;

qryAllSelectedPalmYears (13 records):
SELECT CStr(PID & "_" & theYear) AS PID_Year FROM tblCensusData WHERE
PID & "_" & theYear IN (SELECT PID & "_" & theYear FROM
tblCensusData);

I ran an unmatched query on these in A97 and didn't get the expected
5_2006 value. So I tried converting these to Make Table queries and
ran an unmatched query on the resulting tables to see which palms were
missed. To turn the union query into a Make Table query in A97:

SELECT PID_Year INTO tblAllPalmYears FROM [SELECT
CStr(tblCensusData.PID & "_" & tblCensusData.theYear) As PID_Year FROM
tblCensusData, tblCensusData AS tblCensusData_1 UNION SELECT
CStr(tblCensusData.PID & "_" & tblCensusData_1.theYear) As PID_Year
FROM tblCensusData, tblCensusData AS tblCensusData_1]. qryMaketable;

The other query becomes:
SELECT CStr(PID & "_" & theYear) AS PID_Year FROM tblCensusData WHERE
PID & "_" & theYear IN (SELECT PID & "_" & theYear FROM
tblCensusData);

After running those Make Table queries:

qryMissingPalmYears:
SELECT DISTINCTROW [tblAllPalmYears].[PID_Year] FROM tblAllPalmYears
LEFT JOIN tblAllSelectedPalmYears ON [tblAllPalmYears].[PID_Year] =
[tblAllSelectedPalmYears].[PID_Year] WHERE ([tblAllSelectedPalmYears].
[PID_Year] Is Null);

!qryMissingPalmYears:
PID_Year
5_2006

It was a little involved but hopefully it will allow you to find the
missing census palm-years. The queries are not very efficient so post
back if you need something better or to let me know that my guesses
weren't close.

James A. Fortune
CDMAPoster@FortuneJames.com