missing records in my form!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2007 to store lots of nested data - subsets of subsets. I
have developed a form to be able to quickly enter the data into the database.
However, i can't see all of my records when i scroll through. Any ideas why?

Also, the records that I can't see in the form don't turn up in my query,
even though I know that they contain data that should be encompassed by the
query.

I welcome any suggestions!
 
Can you please explain that in non-technical language?! (I'm a biologist
using computers, not a computer whizz!)

The data has all been entered in the same way, via the form, so I don't
understand why there would be a difference between the early entries and the
later entries...

thanks!

buggirl
 
Query design flaw <g>
Related to Null, Empty Strings?

Pieter










- Show quoted text -
Click to expand...

Show us some (a FEW) sample records... some that DO show, some that DO
NOT. Some from the parent table and the child table. Then we can
figure it out. If you do a standard join in Access, you get an inner
join, which acts like a filter/Where clause. Say you have two tables,
A and B, and you join them with an inner join...

SELECT A.*, B.*
FROM A INNER JOIN B ON A.PrimaryKeyField=B.ForeignKeyField;

you will only get records returned where the values in PrimaryKeyField
and ForeignKeyField match. Try changing the INNER join to a LEFT or
RIGHT join by clicking on the join line in design view and changing it
to a type 2 or 3 join (Left or right). If it returns values, then you
have no matches between the two tables....
 
Hi Pieter,

Thanks for your help. I'm still working on the problem. I've been looking at
the way my tables are joined in the query.

I'm not sure how I can show you a few sample records. Let's see if I can
simply explain the data base to you.

We're sampling pools within a river. We visit several sites and sample
several pools at each site. Hence, pools are nested within sites.

My first table describes site conditions. The primary key is 'sample id',
which is a unique descriptor for each site. The input mask for this is
LLL\-00\-000;;_
Click to expand...

The second table describes pool conditions. We sample three or four pools
within a site. The primary key for this table is actually two columns:
'sample id' and 'pool id'. Pool id is a unique number (recorded as 'text')
for each pool. The same pool may be sampled on a number of occasions, so it's
possible to have the same pool id with different sample ids.

The third table is for subsamples taken from within pools. Sometimes we take
one subsample and sometimes we take three (depending on the project). Again,
these are numbers recorded as text. If only one subsample is taken, I record
it as '1'. If three subsamples are taken, they are recorded as '1', '2' and
'3'.

Make sense?

I think the problem is with the sample id. I thought I was entering it in
the same way in every table, every time. But things have gone awry somewhere.
I notice in my form that sometimes my sample id looks like this: ASB-07-001
and sometimes like this: ASB07001, even within the same form (!!). [Note that
the input mask includes the dashes.]

If my sample id is being recorded in different ways in each of the tables,
then I guess the join won't be recognising that they are the same thing.

So, why do they look different?! Surely, if they're all being entered in the
same column they should be the same?! Aargh!

thanks again!
 
Oh!

I just had another look at the subsample table in my database and found
something else a bit funky.

When I click on the + symbol to open up the subdatasheet, some of the
records have empty subdatasheets and some of them do contain data. The
records with the empty subdatasheets are the ones not showing in my query.
The records with filled subdatasheets do show in my query.

I don't really understand how this could be possible, all in the same
datasheet. Wierd.

I welcome any feedback!

buggirl
 
Sounds like you're not enforcing referential integrity. If you did,
you wouldn't be able to enter data into child tables without a parent
record already existing...

You'd have to fix the inaccurate ID's first, and then you can create
the relationships. Then at least you won't have to go through this
anymore. You'll at least get a message about key violations if you
try to enter incorrect foreign keys...
 
Thank you! I had to go back and re-enter a bunch of data - it took a bit of
fooling around but now it works perfectly! I'm glad I got this problem sorted
out before my database got any bigger.

thanks again!
 
Back
Top