Query to Check Number Of Fields Populated 1

[Swi]

I have the following fields in my table:

FullName
Company
Address1
Address2
CSZ

I would like to have a query that would provide the following output.

# of 5 Liners = 50
NC12C 50 instances
# of 4 Liners = 25
N12C 13 instances
NC2C 12 instances
# of 3 Liners = 40
C1C 15 instances
N1C 15 instances
N2C 10 instances
etc...

Note that I want the output to let the user know how many 5 liners, 4 liners, etc... there are and what instances are possible as well as how many of each instance there are. Can all of this be done in one query or should they be done in seperate queries? Any help is appreciated. Thanks.


Swi

 

[dhookom]

You are assuming we know what a "5 liners" is. Is "N12C" a value in a field?

I think I would start by creating a union query. This would be made fairly simple if there was a primary key in the table but you don't seem to have one. Can you add one?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Swi]

I would like a 1 byte value to be assigned to each field (it could be the first letter of the field name) to see what instances exist based on field population. A five liner would have all fields populated. Does this make more sense?

Swi

 

[dhookom]

Did you consider this?

I think I would start by creating a union query. This would be made fairly simple if there was a primary key in the table but you don't seem to have one. Can you add one?

I am still confused by "NC12C 50 instances". Perhaps you could take the time to provide a few records and an example desired output.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Swi]

A primary key would not always be available because layout of the table may change from time to time. I plan to have the user choose what fields they want to analyze.

As for the "NC12C 50 instances".

Let's say we assign a 1 byte character to each field being analyzed. Ex. -

FullName = N
Company = C
Address1 = 1
Address2 = 2
CSZ = Z

So, I would like to know how many records have a combination of NC12Z or N12Z or N1Z etc... in addition to knowing how many records have 5 lines present, or 4 lines present, etc...

Example of report format:

DATA CONTENT REPORT V1.0
9/6/2007 4:08:57 PM
----------------------------------------------------------------------------------------------------
MATRIX: COUNT
FADCSZ 1
F DCSZ 2,275

LINES POPULATED: COUNT
6 1
5 2,275
----------------------------------------------------------------------------------------------------
INPUT FILE----------------------> C:\BCC\MM2010\LISTS\8561 HANOVER HOSPITAL.DBF
INPUT COUNT---------------------> 2,276
REPORT FILE---------------------> C:\TEST.TXT
====================================================================================================
FIELD NAME FIELD LENGTH
====================================================================================================
FULL_NAME 50
ALTADDR1 50
DELADDR 50
CITY 30
STATE 2
ZIPCODE1 10

Thanks.

Swi

 

[dhookom]

If you can't add a primary key, try create a query like:

SELECT IIf(Len(FullName & "") > 0,"N","") & IIf(Len(Company & "") > 0,"C","") & 
IIf(Len(Address1 & "") > 0,"1","") &  IIf(Len(Address2 & "") > 0,"2","") & 
IIf(Len(CSZ & "") > 0,"C","") As NC12C
FROM tblNoName

Save the query with the name "qselNC12C". You can then create queries from this like:

SELECT NC12C, Count(*) as NumOf
FROM qselNC12C
GROUP BY NC12C;

and

SELECT Len(NC12C) as NumLines, Count(*) as NumOf
FROM qselNC12C
GROUP BY Len(NC12C);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Swi]

What would be your opinion on the performace hit for multiple queries like this? Also, what would your solution be if a primary key was created? Thanks.

Swi

 

[dhookom]

The performance of multiple queries shouldn't be an issue. Try it. It might depend on too many characteristics of your data, network, PC,...

I'm not sure what I would do now if you had a primary key. Initially I didn't have a clue regarding your needs since you hadn't adequately explained the "NC12C".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Swi]

Thanks for the pointers. I will give it a shot. Thanks.

Swi