generating data entirely from code (?)

[pullingteeth]

Let me tell you first what I'm trying to do, then what I've tried.

I have three tables (simplified) thus:

User
----
username:string

Records
-------
record_number:integer
status:integer
date:datetime

RecordErrors
------------
record_number:integer ---> Records.record_number
description:string
date:datetime


What I need to do is generate a report for all users showing their error percentage between two given dates. To compute this, I need to do the following calculation for each user:
x (number of records in x status by user between dates) / y (number of errors where the record was made by the user between dates) * 100

Since x and y are unrelated, I can't retrieve them in one query. Thus I thought of doing two queries, to get all the data for all users, and combining the queries programmatically - however I can't work out how to get a report to source its data from code. I'm using Access 97.

Thanks

 

[dhookom]

You shouldn't need any code. I don't know quite what you need since you don't have a user or username field in either the Records or RecordErrors table.
If RecordErrors is linked to Records by Record_Number, why would you need a date field in both tables? Wouldn't they contain the same value?

Also, Date is a poor name for a field since it is a function name and returns its own value.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pullingteeth]

I just made the names of the fields up for example's sake, and didn't include fields not relevant to this query; to answer your questions:

1) Sorry, Records should have a user field. My bad.

2) I need a date for both tables because the date in Records is the date that the status is set; the date in RecordErrors is the date that the error was recorded (since multiple errors can be recorded for one record, it is part of a composite primary key)

Let me give you some example data:

User:
-----
bob
jane
mary

Records:
--------
1, A, 5/7/03, bob
1, C, 5/7/03, jane
2, C, 5/8/03, jane
2, D, 5/7/03, mary
3, A, 5/6/03, bob
3, B, 5/7/03, bob
3, C, 5/7/03, bob
3, D, 5/7/03, bob
5, C, 3/2/03, henry

RecordErrors:
-------------
1, "bad grammar", 5/19/03
2, "bad spelling", 5/19/03
2, "incorrect", 5/19/03
5, "incorrect", 5/19/03


My pseudocode is something like:

FOR EACH user in users:
ErrRate = (Num Records in C status by user) / (Num RecordErrors referring to Records in C status by user)
Print ErrRate
NEXT user

i.e.
bob: 1 / 0 => 0%
jane: 2 / 2 = 100% (NB this isn't a "true" 100% by example)
mary: 0 / 0 = 0%

Is there a way to express this, or similar, in SQL?

Thanks

 

[dhookom]

I don't understand your data. If the numbers in RecordErrors reference the numbers in Records, then how do you tell which user made the error. You have two values of "2" in both tables. One is for Jane and one for Mary and in the RecordErrors table one is bad spelling and the other incorrect.

I can't figure how your "i.e." fits with your sample data.

Maybe someone else has an idea of how this fits.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.