It looks to me that the relational light has not come on for you. It is
difficult when you come to it from a backgroundof solving things another
way.
It sounds like you have a main table. I do not know what you call it. It
would contain something like id_main key and stuff just about the whole of
whatever "main" is.
You will probably require one table for each inspection point type, if they
are different. You might be reading voltage at 30 points, they only require
one table containg Inspection_point_number and voltage. You might measure
pressure at 10 others, and you would only need one table for these
containing Inspection_point_number and pressure. Sometimes you might want
to put these both into one table, and have another field identify whether
the reading is voltage or pressure. This is not usually a good idea.
InspectionPointsType1Reading
id_IP key (this is just to identify this record. It could be an autonumber
that does not have any other function but to identify this record.)
Inspection_point_number number (this identifies an individual inspection
point. There will be many records with this number.)
Reading_identifier number? (This will identify a particular reading)
Reading_value number
I would expect there to be unique values for the combination of
Inspection_point_number and Reading_identifier.
You can then write queries that will gather information about a specific
Inspection_point_number, or queries about a particular reading,
This can only be a rough idea, I do not know enough about your particular
application.
Good luck.
MikeLostinWoods said:
Ughh, my head hurts. Originally I tried to seperate the main table, but I
couldn't get the queries or forms to work right. They work great now, and
I'd hate to have to alter them. Can these other tables be created from
the
operating one? Or used in conjuction with? Eventually my goal is to
generate a report that lists "3" level deficiencies with a location,
comments, and date reported fields, all of which are drawn from the main
table. I also need to be able to have repetative data, so that multiple
deficiencies on one record are listed independantly, possibly even in
other,
more specific reports. I thought about a month ago I might need to scrap
the
whole thing and start again with 100 individual tables for each inspection
point. Layering the qeries didn't work like I wanted it to either. They
brought back records with a 3 but did not exclude other statuses.
David F Cox said:
Sorry, Allen is even righter
-<)
David F Cox said:
Alan is quite right.
meanwhile:-
3 IN ([field1], [field2], ... good luck with your typing, such are the
perils for those that stray from the path "Relational"..
Mike, you need to use a relational data design.
Presumably there are some 200+ different conditions you are trying to
monitor, and any one of those can occur at any date?
If so:
1. Create a table that has a *record* for each of the possible issue
types. This IssueType table might have this field:
IssueTypeID Text 24 characters. Primary key.
2. Create another table that records the issues that occur from time
to
time:
IssueID AutoNumber primary key
IssueTypeID Text 24 character. Relates to
IssueType.IssueTypeID
IssueLevelID Number 1 to 3, indicating the level of the
issue.
You now have *one* field to query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
message
I have a table. The table has a little over 200 fields. About half
of
these
fields are filled from check boxes on a form returning a value of 1,
2,
or 3
(Representing statuses of ok, uh-oh, and oh my God!). I need to make
a
query
that returns all the records where any field matches 3. OR in
criteria
looked like it was going to do the job, but it ran out in design
mode.
Is
there an easy SQL to do this? Or do I need to hack it up into pieces
in
multiple queries and combine them again later?
