Solved: VB / SQL Query Help

[dondark]

I have a table that records errors generated during document reviews. The table is strucuted (roughly) in the following format:

Document, Review #, Error Type, Detail

Basically, there is a document review which will produce a number of errors (say 5-10), and each error is captured as a line item in the table.

I want to query this information to obtain a summary of each unique combination of Document and Review # combination. So If I have 15 records that have the same Document values and Review values, the query will produce a line item like this: Document, Review #, 15.

Any help or suggestions?

[asingh]

Hi,

Push all your data into another table [basically an append query]. In the table structure, create a two-way primary key. Apply the primary key on Document and Review,togethar.

But I would also suggest to make the primary key three-way, being on Document, Review, and Error. That way your table will be completely unique. [Normalized..the way a data base should be]

No need for VBA here....!

regards,

asingh

[geekgirlau]

Create a totals query: group by Document and Review #, then count Review #.

[vba]
SELECT [Document], [Review#], Count([Review#]) AS CountReview
FROM MyTable
GROUP BY [Document], [Review#]
[/vba]

[dondark]

Hi,

Push all your data into another table [basically an append query]. In the table structure, create a two-way primary key. Apply the primary key on Document and Review,togethar.

But I would also suggest to make the primary key three-way, being on Document, Review, and Error. That way your table will be completely unique. [Normalized..the way a data base should be]

No need for VBA here....!

regards,

asingh

Thanks for the help!

I'm fairly good with VB, but my follow-up question is DB 101. In terms of basic design, do I have my main data table strucuture correctly? Or would it be better / more efficient to have two separate tables. The first capturing Document, Review #, and the Total # of Errors, and then the Second Table capturing Error details, which one of the fields being the review input?

Thanks again.

[stanl]

All talk of textbook normalization aside, your design could derive from a few simple questions.

0. Can each document be assigned a unique id

1. Can the same document be reviewed multiple times.

2 If yes, can it be reviewed multiple times on the same day

3. If no, consider a combination of documentID+yyyymmdd [or basically an 8-char date field] as a key to relate between your document master and detail file, If yes, your key would be documentID+DocNum+yyyymmdd

This gives you some flexibility in ascending/descending arrangements in your reports, or report on all documents for a single day, or a single document over a month's period.

.02 Stan