SQL Query to Create Summary

[aga2957]

Hi Guys!

It's me again.

From one of my tables, custinfo, i want to generate a summary of records. What I mean is I want to count the total number of records, count those who doesn't have address entries, count those who doesn't have credit records. So in presentation, I want to have this:

Field No Records With Records Total
Address 10 256 266
CreditREc 5 261 266

Is it possible?

Thanks!

 

[stopher]

You can do this with a series of queries that you UNION and JOIN together. However, depending on how you want to use the info, it's probably easier to use DCOUNT, e.g. your first value (count of addresses not entered) would be:
=DCount("[Accno]","[CustInfo]","[Address] is null")

I've assumed your unique record identifier is Accno. You can stick this in an unbound text box in a report or form. The report or form doesn't have to be based on any underlying data.

The other data would be done the same with variations of the same function.

hth
Stopher

 

[aga2957]

Is there a way where I can do this in SQL? I don't actually need to create a form since I'm going to transfer the query to excel.

If there's none, it will be alright.

Thanks a bunch!

You can do this with a series of queries that you UNION and JOIN together. However, depending on how you want to use the info, it's probably easier to use DCOUNT, e.g. your first value (count of addresses not entered) would be:
=DCount("[Accno]","[CustInfo]","[Address] is null")

I've assumed your unique record identifier is Accno. You can stick this in an unbound text box in a report or form. The report or form doesn't have to be based on any underlying data.

The other data would be done the same with variations of the same function.

hth
Stopher

 

[stopher]

You could try this...

SELECT "Address" AS Field, Sum(IIf([Address] Is Null,1,0)) AS Blank, Sum(IIf([Address] Is Null,0,1)) AS Complete, Count(AccNo) AS Total
FROM CustInfo
GROUP BY "Address"
UNION SELECT "CreditRec" AS Field, Sum(IIf([CreditRec] Is Null,1,0)) AS Blank, Sum(IIf([CreditRec] Is Null,0,1)) AS Complete, Count(AccNo) AS Total
FROM CustInfo
GROUP BY "CreditRec";

I don't know what you table name is so I've called it CustInfo here. I assume CustRec is also a text field (?). There may be better ways to do this.

Stopher