Passing a value from a table to a criteia

[scotlin]

I have a table which has several separated values in a field.
e.g >> u10 u11 u12
If I use one of these value as a criteria in a select query I can list all the records where a particular value eg u12 appears. There is also a need to produce a list of all the values grouped by u number.
I have been thinking of using a table of all the u numbers and reading them into an append wuery to create a temporary table which I can then run a query / report to group them....

It seems very complicated. I am a newbie to this and I am sure that there must be an easier way...

Can anyone help?

 

[joeselch]

There is an easier way... construct your tables so you aren't storing multiple values in a field.

To handle the U numbers will require three tables:

• the table you've asked about
• a table of U numbers
• a table linking records from the first table to records in the U number table

Do yourself a favor and learn about data normalization now - before you go any further.
Try searching these forums for "normalization" and, once you've got a handle on that, search for "many-to-many" (I know Pat Hartman has posted an excellent example).

 

[scotlin]

Thanks for that Joe.
At the moment my link table has 3 fields. a clubid;a list of Unumbers;and a person id.
Are you suggesting that I should create a separate record for each unumber?
This would mean, in some cases, having 6 records with the same clubid and person id.

I had this and changed to the present format because it caused me other issues.
I had to be able to create a report in the format:
personid--- clubid-- unumber;unumber;unumber. I didn't know how to acheive this so I suppose I bodged it. I can revert if that would be better but I still need that output.

Did I say that I was a newbie or have you guessed...

 

[joeselch]

How are the three things related - personid, clubid, unumber?

What is the table structure of your database?
I suspect you might have a table of persons and a table of clubs. And, if people can belong to more than one club, you have a many-to-many relationship between tables persons and clubs - that is, a table linking records in tables person and clubs.
Assuming this is correct, how do the unumbers fit in?

 

[scotlin]

It gets more complicated...

As you correctly say I have a persons table, a club table, a school table all self expllanatory, I think. I also have a table which TRIES to link coaching agegroups at both school and clublevel.
This table will have:
cid from person table
organisation eg sabc = school with the code abc in the school table or cefg = club with the code efg from the club table.
agegroup This is not linked to any table but identifies the agegroup which CID deals with. This can be either at club or school or both. This means that the same person can have more than 2 records.
This is ok if I run a query to select all the u11 coaches for example. I can also run a query / report to list all coaches grouped by age, school or club. What I cannot seem to do is list by person with all the responsibilities they have eg school x U10, u11 and club x u13 u14.
This is why I "bodged" the field to include all the Unumbers.
This way I can do almost everything except query / report all agegroups grouped in agegroups.

Thanks for you interest and persistance...

 

[joeselch]

Here is an example that may get you started... tables, queries, forms, reports.

hope this helps