Using IIF with multiple values

  • Thread starter Thread starter Luther
  • Start date Start date
L

Luther

Hello,

I have a query like this:

SELECT Sum(IIf([Source1]="03A",1,0)) AS HowMany, Count(*)
AS TotalRecords, [HowMany]/[TotalRecords] AS PercentUpdated
FROM HiPlanNames;

What I would like to do is:

- scan 6 fields (Source1 - Source6) and find the value
03A and add them up. Basically, this value can be in any
of these fields; there won't be no duplicates.

- the trick is that I have to do this for 8 more values
(03B, 03C, 03D etc..), calculate the percent and display
them like this (a table would be just fine):


03A 200 10.0%
03B 120 12.0%


Any help would be greatly appreciated. Thanks.
 
You may want to rethink your database design.

In general, having field names like Source1, Source2, ... implies that you
haven't normalized your tables properly. Instead of storing the data as six
fields in Table1, you should add a second table so that each of the 6 values
is a separate row in Table2. Then what you want to do becomes simple SQL.
 
Thank you for responding.

I can rename these fields anything I want; they just
designate which source was used to make a decision about
the record (thought process too complicated to
explain)...I didn't think that "Source" is a reserved
keyword in Access. but with your suggestion, can you
elaborate? Does table2 need to be linked to table1 by ID?
How would you construct the SQL?

Many thanks in advance.
-----Original Message-----
You may want to rethink your database design.

In general, having field names like Source1, Source2, ... implies that you
haven't normalized your tables properly. Instead of storing the data as six
fields in Table1, you should add a second table so that each of the 6 values
is a separate row in Table2. Then what you want to do becomes simple SQL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello,

I have a query like this:

SELECT Sum(IIf([Source1]="03A",1,0)) AS HowMany, Count (*)
AS TotalRecords, [HowMany]/[TotalRecords] AS PercentUpdated
FROM HiPlanNames;

What I would like to do is:

- scan 6 fields (Source1 - Source6) and find the value
03A and add them up. Basically, this value can be in any
of these fields; there won't be no duplicates.

- the trick is that I have to do this for 8 more values
(03B, 03C, 03D etc..), calculate the percent and display
them like this (a table would be just fine):


03A 200 10.0%
03B 120 12.0%


Any help would be greatly appreciated. Thanks.
Click to expand...


.
Click to expand...
 
My comment had nothing to do with the names of the fields, but the fact that
you've got six of them in the same row.

Table2 would have a foreign key pointing back to the primary key of Table1.

In other words, if your current design is something like:

Table
------------
ID
Field1
Field2
Source1
Source2
Source3
Source4
Source5
Source6

your two tables would be something like:

Table1
----------
ID
Field1
Field2

Table2
----------
Table1ID
SourceNb
Source

where Table1ID would contain the same value as ID in Table1 for the
corresponding rows, SourceNb would be 1, 2, ...6 and Source would be
whatever Source1, Source2, ... Source6 contained in the original table.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Luther said:
Thank you for responding.

I can rename these fields anything I want; they just
designate which source was used to make a decision about
the record (thought process too complicated to
explain)...I didn't think that "Source" is a reserved
keyword in Access. but with your suggestion, can you
elaborate? Does table2 need to be linked to table1 by ID?
How would you construct the SQL?

Many thanks in advance.
-----Original Message-----
You may want to rethink your database design.

In general, having field names like Source1, Source2, ... implies that you
haven't normalized your tables properly. Instead of storing the data as six
fields in Table1, you should add a second table so that each of the 6 values
is a separate row in Table2. Then what you want to do becomes simple SQL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello,

I have a query like this:

SELECT Sum(IIf([Source1]="03A",1,0)) AS HowMany, Count (*)
AS TotalRecords, [HowMany]/[TotalRecords] AS PercentUpdated
FROM HiPlanNames;

What I would like to do is:

- scan 6 fields (Source1 - Source6) and find the value
03A and add them up. Basically, this value can be in any
of these fields; there won't be no duplicates.

- the trick is that I have to do this for 8 more values
(03B, 03C, 03D etc..), calculate the percent and display
them like this (a table would be just fine):


03A 200 10.0%
03B 120 12.0%


Any help would be greatly appreciated. Thanks.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top