STILL counting records...

[merciless32]

ok...how about this?

How would I use DCount to count records meeting a certain criteria in several tables? I need these totals rolled up into a form or unique table? I can do it easily when only one table is involved. The problem I am having is that the data has to come from 15 different tables. What would the code look like for multiple tables? Thanks in advance.

 

[Mile-O]

Why do you have 15 tables?

 

[merciless32]

Each table is a seperate data entry form. The powers that be want it structured that way. In each for is a field called "Status" that records where the form is in the process. I need a rollup of each form based on this field. Make sense?

 

[Mile-O]

Are all these tables identical in structure?

If so, you should normalise them into one table, creating an extra field that identifies their status.

This way, you'll be able to query on status and find much greater solutions and reduce your problems.

 

[merciless32]

Nope...they are all different. There is no constant in them except for the Status field.

 

[Mile-O]

I suppose you could use a bit of code to the counting part and add all 15 totals together to get the final result.

 

[merciless32]

Gonna try a crosstab query to update a seperate table. maybe then I can pull in my counts.

 

[Pat Hartman]

Use a union query and then a crosstab query if you want to pivot the results.

Select "tbl1" As TableName, Status, Count(*) As StatusCount
From tbl1
Group By TableName, Status
Union Select "tbl2" As TableName, Status, Count(*) As StatusCount
From tbl2
Group by TableName, Status
Select ....

 

[merciless32]

Got it...thanks...

I decided to create a form and Dcount each table in unbound fields. Then I just totals those fields on the form. Also made it easy to create a report from the form. Thanks to everyone who posted!

God bless!