How to link tables via the field names themselves, not their conte

[Guest]

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

 

[Guest]

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.

 

[Guest]

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!!

 

[Guest]

Novice,

True, the structure I recommend will make the table larger than yours is,
but with my structure, you can query the database to do what you want easily.
Well structured databases are not normalized to make them smaller, they are
normalized to make them faster. If you want to make your database somewhat
smaller, you could create an Audit_Date table (Audit_ID, Audit_Date) so that
the normalized table would contain the long integer value of the Audit_ID.

Because so many of us are more comfortable with spreadsheets, and because it
is frequently easier to arrange our "data" in the format you are using, many
programmers fall prey to that structure. Although more difficult to develop,
applications with well normalized table structures are much easier to query.

To solve your immediate problem, I would create "Normalizing Query" that
would look something like the following. You will have to write this in the
SQL view as the query grid will not support Union queries.

SELECT [Contract Number], [Audit Date], "QA" as Source, "Stat 1" as
Statistic, [Stat1] as Score FROM tbl_QA
UNION ALL
SELECT [Contract Number], [Audit Date], "QA" as Source, "Stat 2" as
Statistic, [Stat2] as Score FROM tbl_QA
UNION ALL
SELECT [Contract Number], [Audit Date], "QA" as Source, "Stat 3" as
Statistic, [Stat3] as Score FROM tbl_QA
UNION ALL
....
SELECT [Contract Number], [Audit Date], "HS" as Source, "Stat 1" as
Statistic, [Stat1] as Score FROM tbl_HS
UNION ALL
SELECT [Contract Number], [Audit Date], "HS" as Source, "Stat 2" as
Statistic, [Stat2] as Score FROM tbl_QA
UNION ALL
.....

This query may take a while to run, so you might want to consider saving it
and using it as the source for a MakeTable query. You could run this Make
Table once a day to build the up-to-date database, or you could modify the
above so that it only captures the records for a range of dates and appends
them to a permanent ("working table").

Once you have your data in this format, you can link it to your
classification table by Source and Stat Name, or just Stat Name, and then you
can easily write queries to analyze at the statistic, statistic class, or any
other level.

HTH
Dale

 

[Guest]

Hi Dale

I will give this a go and yell if I need more help!!

Thanks in advance and for your time on this one

Cheers

--
an access novice

Novice,

True, the structure I recommend will make the table larger than yours is,
but with my structure, you can query the database to do what you want easily.
Well structured databases are not normalized to make them smaller, they are
normalized to make them faster. If you want to make your database somewhat
smaller, you could create an Audit_Date table (Audit_ID, Audit_Date) so that
the normalized table would contain the long integer value of the Audit_ID.

Because so many of us are more comfortable with spreadsheets, and because it
is frequently easier to arrange our "data" in the format you are using, many
programmers fall prey to that structure. Although more difficult to develop,
applications with well normalized table structures are much easier to query.

To solve your immediate problem, I would create "Normalizing Query" that
would look something like the following. You will have to write this in the
SQL view as the query grid will not support Union queries.

SELECT [Contract Number], [Audit Date], "QA" as Source, "Stat 1" as
Statistic, [Stat1] as Score FROM tbl_QA
UNION ALL
SELECT [Contract Number], [Audit Date], "QA" as Source, "Stat 2" as
Statistic, [Stat2] as Score FROM tbl_QA
UNION ALL
SELECT [Contract Number], [Audit Date], "QA" as Source, "Stat 3" as
Statistic, [Stat3] as Score FROM tbl_QA
UNION ALL
...
SELECT [Contract Number], [Audit Date], "HS" as Source, "Stat 1" as
Statistic, [Stat1] as Score FROM tbl_HS
UNION ALL
SELECT [Contract Number], [Audit Date], "HS" as Source, "Stat 2" as
Statistic, [Stat2] as Score FROM tbl_QA
UNION ALL
....

This query may take a while to run, so you might want to consider saving it
and using it as the source for a MakeTable query. You could run this Make
Table once a day to build the up-to-date database, or you could modify the
above so that it only captures the records for a range of dates and appends
them to a permanent ("working table").

Once you have your data in this format, you can link it to your
classification table by Source and Stat Name, or just Stat Name, and then you
can easily write queries to analyze at the statistic, statistic class, or any
other level.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

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!!