IIf there are 24 entries tick yes?

[SusanC]

Hi guys,
I'm in need of some help!!!

I have two tables:
Table 1 holds information about a experiments, i.e. 1A, date started, equipment used etc. There are 24 parts to an experiment, i.e. 1A1, 1A2, 1A3 - 1A24
Table 2 holds information about each part. i.e. 1A1, used 5 people, 8 cars etc.

I have a query based on Table 1 which looks like:
Experimental Code Finished?
1A yes
1B no
1C yes

How can I get the query to look up the code in the first 'expeirmental code' field, i.e. 1A of the query, relate it to whether there are 24 entries like this code in Table 2, and then either tick a box or say yes or no in the finished? column of the query?

IIf(Table2!code) is like (Query![Experimental Code]) and there are 24 entries, then 'yes' or tick!

I know this isn't written properly but I thought it may help explain my problem.
I just don't know how to make it relate to specific field of each query record?
any help would be great!
cheers
Sue

 

[Mile-O]

Can you detail your table structure (in detail) as what you have described is certainly not normalised? Having the tables normalised will make everything much easier.

 

[SusanC]

Can you detail your table structure (in detail) as what you have described is certainly not normalised? Having the tables normalised will make everything much easier.

What does normalised actually mean?!

I'll have a go at explaining:

Table 1 has
Experimental Code Date started Experiment Finished?
1A 01.01.01 no
1B 01.02.01 no
1C 01.03.01 no

Table 2
Experimental Code No of equipment
1A1 5
1A5 3
1B13 9
1C1 1
1A3 3

Each experiment has 24 codes. When 24 of them have been entered into the table 2 then the experiment has been finished. I want the 'Experiment Finished?' field in table 1 to say yes or tick when table 2 contains 24 of that particular code.
does that help?

 

[SusanC]

What does normalised actually mean?!

I'll have a go at explaining:

Table 1 has
Experimental Code Date started Experiment Finished?
1A 01.01.01 no
1B 01.02.01 no
1C 01.03.01 no

Table 2
Experimental Code No of equipment
1A1 5
1A5 3
1B13 9
1C1 1
1A3 3

Each experiment has 24 codes. When 24 of them have been entered into the table 2 then the experiment has been finished. I want the 'Experiment Finished?' field in table 1 to say yes or tick when table 2 contains 24 of that particular code.
does that help?

Sorry I forgot the page doesn't hold the layout as it should so there should be gaps between the different columns.

 

[Jon K]

You can do it with a series of two queries.

In the attached sample database, Table2 contains records 1A1 to 1A24. When the second query is run, the [Experiment Finished?] field in Table1 for code 1A will be ticked.

 

[SusanC]

You can do it with a series of two queries.

In the attached sample database, Table2 contains records 1A1 to 1A24. When the second query is run, the [Experiment Finished?] field in Table1 for code 1A will be ticked.

Ooo you are good at this aren't you?!!
Thank you so much for that, it's going to save me so much time!
thanks again
Sue

 

[SusanC]

Ooo you are good at this aren't you?!!
Thank you so much for that, it's going to save me so much time!
thanks again
Sue

That worked really well.
How would I change it to meet codes like 123A-1A1? I thought about changing the number from 2 to 8 but this didn't work? Can this be done?

 

[Jon K]

If the length of Experimental Code in Table1 varies e.g.

[Experimental Code]
1A
12A
1B
12B
123A

then Table2 must contain the same code, with the sub-code of 1 to 24 being put in an independent field e.g.

[Experimental Code] [Sub Code] [No of equipment]
1A			     1		      5
1A			     3                3
1A			     5		      3
12A			     1		      4
12A			     2		      6
12A			     3		      9
123A			     1		      4
123A			     2		      5
123A			     3                6
1B			    13		      9 
1C			     1		      1

The Left() function is no longer needed in the query "qryOne".