RE: How to link tables via the field names themselves, not their c
- From: accessnovice <accessnovice@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 1 May 2007 05:37:00 -0700
Hi Dale
Thanks for your reply. I understand what you are saying but I am not sure it
can solve my particular problem.
I am trying to capture information taken after QA and site safety audits.
There are approximately 30 different aspects (or statistics) which are
auditted during a visit, split roughly 50:50 between QA and H&S.
At the moment, the information is captured, on paper, as yes/no type info
with comments per aspect (or statisic). What I have to do is capture the same
information but use a scoring system (1 to 5), rather than a yes/no type
system.
I have set up the dbase so that there are 2 tables; a QA table and a H&S
table. Each table comprises approx 15 columns (1 per stat) with the rows
recording the scores, the date of the audit and the contract number. The
content of each table is therefore a series of numbers.
Mulitple audits are made per contract. The scoring idea was to be able to
'rate' each visit and compare audits numerically through the life of the
contract.
eg
Contract Number Audit Date Stat1 Stat2 Stat3 Stat4
C00001 01 May 2006 1 2 4 5
C00001 31 Oct 2006 5 3 5 5
C00002 01 May 2007 1 3 5 5
Just to make things a little more complicated, the statistics are also
classified in terms of their 'type' ie whether they are to do with welfare,
project management, QA, lists and registers, COSHH etc. At the moment, the
classifications are assigned via a separate table (stats class table) where
each of the 30 statistics are listed and a classification is assigned via a
combo from another table again. I have effectively 'converted' the column
headings from the QA and H&S tables ie the field names, into the contents of
the statistic classification table.
eg
Stat Name Stat Class
Stat 1 Welfare
Stat 2 Project Management
Stat 3 QA
Stat 4 COSHH
The stat information is entered into the relevant tables via forms, one for
QA and one form H&S. Each form has all the fields from a table listed with
text boxes for entering the score for each statistic.
Within each table, I wanted to be able to create reports to summate scores
on a per contract basis, or by classification or by statistic. But, I cannot
create a report on this basis as there is no link between the QA or H&S
scores tables with the stat classification tables
Beacuse there are so many stats per contract, and multiple visits per
contract, if I converted the format so that I had
Contract AuditDate QA or H&S Statistic Score
the size of the table would be huge very quickly.
Can I auto create tables - maybe one table per contract? But if so, I would
need to be able to create reports without knowing the table name
Hope you can shed some light on this!!
--
an access novice
"Dale Fye" wrote:
Novice,.
Since you didn't give us any details about your table structure (I recommend
a sample of each table in posts like this to make it easier for us to
visualize your data), I'll just have to take a stab at it.
In paragraph 2, you comment about having "relevant stats for the type as
field names and the info stored in the fields". I think what you need to do
is reorganize the table so that you have a single field and store the "Type"
in that field.
The best example of this that comes to mind is a questionnaire database.
Some people set these databases up with the questions as fields, so that you
only have one record for each student, for each test, and the fields represet
the questions, which might look like:
Name Test Q1 Q2 Q3 Q4
Ted 1 5 3 3 1
A better way to structure this table, for analysis purposes is:
Name Test Question Score
Ted 1 1 5
Ted 1 2 3
Ted 1 3 3
Ted 1 4 1
While this takes more storage space, it is much more efficient for running
queries.
This way, you can write a single query to sum the scores for each student
for a single test, and the query would not change for Test #2, which might
have 20 questions. Plus, you don't have to refer to a specific field if you
want the answer to question #20, you just have to indicate that question # in
the Where clause.
If this doesn't help, post back with more details (example data) and I'll
see if I can be of more assistance.
--
Email address is not valid.
Please reply to newsgroup only.
"accessnovice" wrote:
Hi there
I am trying to set up an audit database containing info on QA and H&S. I
have a table for all the statistics so far recorded in a stats table. The
table also records the type of stat (QA or H&S) and the area it covers
(welfare, risk assessments etc).
I also have two tables set up for QA stats and H&S stats for each contract
we run. Each table contains the relevant stats for the type as field names
and the info stored in the fields are numerical scores from 1( 0% correct) to
5 (100% correct). A record is then created (via a form) for each contract for
QA stats or H&S stats as appropriate
I need to try and report on the scores not just by contract but also area
and at the moment, there is no link between the stats table and the scores
tables since the first records text and the second records numeric values.
Can I set up a query to see if the stats table record contents match a field
name in the QA stats or H&S stats tables and if so, how?
Is there a better way of storing the data? I am still designing so have no
real data 'to lose'
Thanks in advance
--
an access novice
- Follow-Ups:
- Prev by Date: Re: Expression not Sorting
- Next by Date: Re: Select Query on a subform within a form
- Previous by thread: RE: ListBox - MultiSelect VBA issue
- Next by thread: RE: How to link tables via the field names themselves, not their c
- Index(es):
Relevant Pages
|
Loading
