Count Query Help

[james_the_midwife]

Hi there,

Sorry if this has been asked many times before- I have searched for the answer already.

I have a database table that looks like this:

PatID Gest Total Total pre 12 weeks Total post 37 weeks
111 9
111 11
111 20
111 39
112 13
112 20
113 7
113 20
113 30
113 37
113 40

It has around 20,000 different rows of data. Currently, the total fields are unpopulated. I would like to run a query that automatically fills these fields, so that the 'total' column is filled with the total number of times 'PatID' is entered, the 'Total pre 12 weeks' is filled with the number of times that 'PatID' occurs when 'gest' is <12, and the 'Total post 37 weeks' is filled with the number of times that 'PatID occurs when 'gest' is >37.

Please someone save me several days work and say this is possible??

Thank you in advance.
James

 

[karenazz]

Hi,

Storing the data in the table probably won't add much value, I would just create a crosstab query, row heading being PatID, Column headings being your total fields including the criteria you mentioned to get the results you need.

Good luck!