Switch, IIf or ?

M

Mykas_Robi

I have a table where each record consists of a series of posting dates,
posting items and amounts. I need to display those posting items and
amounts for each record that meeting a given date criteria. I am able to use
the switch function to but it will only display the first occurrence where
the posting date meets the criteria.k I know the switch returns the first
occurrence; how do I get for a record the all occurrence where the posting
date meeting the criteria.


query

SELECT [Enter Beginning Date] AS Expr2, [Enter End Date] AS expr3, [GROUP
ACCOUNTS RECEIVABLE].[ADVICE#], [GROUP ACCOUNTS RECEIVABLE].LNAME, [PHYSICIAN
GROUP].[GROUP NAME], Switch(([dt posted1]>Expr2 And [dt
posted1]<Expr3),[GROUP ACCOUNTS RECEIVABLE].[ITEM#/INFO1], ([dt
posted2]>[Enter Beginning Date] and [dt posted2]<[Enter End Date]),[Group
Accounts receivable].[item#/info2], ([dt posted3]>[Enter Beginning Date] and
[dt posted3]<[Enter End Date]), [Group Accounts receivable].[item#/info3],
([dt posted4]>[Enter Beginning Date] and [dt posted4]<[Enter End
Date]),[Group Accounts receivable].[item#/info4], ([dt posted5]>[Enter
Beginning Date] and [dt posted5]<[Enter End Date]),[Group Accounts
receivable].[item#/info5], ([dt posted6]>[Enter Beginning Date] and [dt
posted6]<[Enter End Date]),[Group Accounts receivable].[item#/info6], ([dt
posted7]>[Enter Beginning Date] and [dt posted7]<[Enter End Date]),[Group
Accounts receivable].[item#/info7], ([dt posted8]>[Enter Beginning Date] and
[dt posted8]<[Enter End Date]),[Group Accounts receivable].[item#/info8],
([dt posted9]>[Enter Beginning Date] and [dt posted9]<[Enter End
Date]),[Group Accounts receivable].[item#/info9], ([dt posted10]>[Enter
Beginning Date] and [dt posted10]<[Enter End Date]),[Group Accounts
receivable].[item#/info10], ([dt posted11]>[Enter Beginning Date] and [dt
posted11]<[Enter End Date]),[Group Accounts receivable].[item#/info11], ([dt
posted12]>[Enter Beginning Date] and [dt posted12]<[Enter End Date]),[Group
Accounts receivable].[item#/info12]) AS checknum, [Group Accounts
Receivable].[fname], Switch([checknum]=[item#/info1], [debit1],
[checknum]=[item#/info2], [debit2], [checknum]=[item#/info3], [debit3],
[checknum]=[item#/info4], [debit4], [checknum]=[item#/info5], [debit5],
[checknum]=[item#/info6], [debit6], [checknum]=[item#/info7], [debit7],
[checknum]=[item#/info8], [debit8], [checknum]=[item#/info9], [debit9],
[checknum]=[item#/info10], [debit10], [checknum]=[item#/info11], [debit11],
[checknum]=[item#/info12], [debit12]) AS dbt,
Switch([checknum]=[item#/info1], [credit1], [checknum]=[item#/info2],
[credit2], [checknum]=[item#/info3], [credit3], [checknum]=[item#/info4],
[credit4], [checknum]=[item#/info5], [credit5], [checknum]=[item#/info6],
[credit6], [checknum]=[item#/info7], [credit7], [checknum]=[item#/info8],
[credit8], [checknum]=[item#/info9], [credit9], [checknum]=[item#/info10],
[credit10], [checknum]=[item#/info11], [credit11], [checknum]=[item#/info12],
[credit12]) AS crdt
FROM ([GROUP TAX INFO] INNER JOIN [GROUP ACCOUNTS RECEIVABLE] ON [GROUP TAX
INFO].[ADVICE#] = [GROUP ACCOUNTS RECEIVABLE].[ADVICE#]) INNER JOIN
[PHYSICIAN GROUP] ON [GROUP TAX INFO].[GROUP NUMBER] = [PHYSICIAN
GROUP].[GROUP NUMBER]
WHERE ((([GROUP ACCOUNTS RECEIVABLE].[DT POSTED1])>[Enter Beginning Date]
And ([GROUP ACCOUNTS RECEIVABLE].[DT POSTED1])<[Enter End Date])) OR
((([GROUP ACCOUNTS RECEIVABLE].[DT POSTED2])>[Enter Beginning Date] And
([GROUP ACCOUNTS RECEIVABLE].[DT POSTED2])<[Enter End Date])) OR ((([GROUP
ACCOUNTS RECEIVABLE].[DT POSTED3])>[Enter Beginning Date] And ([GROUP
ACCOUNTS RECEIVABLE].[DT POSTED3])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED4])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED4])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED5])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED5])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED6])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED6])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED7])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED7])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED8])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED8])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED9])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED9])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED10])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED10])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED11])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED11])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED12])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED12])<[Enter End Date]));

so after the results are off because if date posted 1 meets the criteria. it
doesn't check to see if date posting2 meets the criteria.

Please help
 
A

Allen Browne

The problem is a classic example of why you need a relational design.

In a relational database, you do not have many repeating columns in the one
table (such as [DT POSTED4], [DT POSTED5], [DT POSTED5], and so on.)
Instead, you create a related table where there can be many related records
for one record in this table, each one with its own posting date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mykas_Robi said:
I have a table where each record consists of a series of posting dates,
posting items and amounts. I need to display those posting items and
amounts for each record that meeting a given date criteria. I am able to
use
the switch function to but it will only display the first occurrence where
the posting date meets the criteria.k I know the switch returns the first
occurrence; how do I get for a record the all occurrence where the posting
date meeting the criteria.


query

SELECT [Enter Beginning Date] AS Expr2, [Enter End Date] AS expr3, [GROUP
ACCOUNTS RECEIVABLE].[ADVICE#], [GROUP ACCOUNTS RECEIVABLE].LNAME,
[PHYSICIAN
GROUP].[GROUP NAME], Switch(([dt posted1]>Expr2 And [dt
posted1]<Expr3),[GROUP ACCOUNTS RECEIVABLE].[ITEM#/INFO1], ([dt
posted2]>[Enter Beginning Date] and [dt posted2]<[Enter End Date]),[Group
Accounts receivable].[item#/info2], ([dt posted3]>[Enter Beginning Date]
and
[dt posted3]<[Enter End Date]), [Group Accounts receivable].[item#/info3],
([dt posted4]>[Enter Beginning Date] and [dt posted4]<[Enter End
Date]),[Group Accounts receivable].[item#/info4], ([dt posted5]>[Enter
Beginning Date] and [dt posted5]<[Enter End Date]),[Group Accounts
receivable].[item#/info5], ([dt posted6]>[Enter Beginning Date] and [dt
posted6]<[Enter End Date]),[Group Accounts receivable].[item#/info6], ([dt
posted7]>[Enter Beginning Date] and [dt posted7]<[Enter End Date]),[Group
Accounts receivable].[item#/info7], ([dt posted8]>[Enter Beginning Date]
and
[dt posted8]<[Enter End Date]),[Group Accounts receivable].[item#/info8],
([dt posted9]>[Enter Beginning Date] and [dt posted9]<[Enter End
Date]),[Group Accounts receivable].[item#/info9], ([dt posted10]>[Enter
Beginning Date] and [dt posted10]<[Enter End Date]),[Group Accounts
receivable].[item#/info10], ([dt posted11]>[Enter Beginning Date] and [dt
posted11]<[Enter End Date]),[Group Accounts receivable].[item#/info11],
([dt
posted12]>[Enter Beginning Date] and [dt posted12]<[Enter End
Date]),[Group
Accounts receivable].[item#/info12]) AS checknum, [Group Accounts
Receivable].[fname], Switch([checknum]=[item#/info1], [debit1],
[checknum]=[item#/info2], [debit2], [checknum]=[item#/info3], [debit3],
[checknum]=[item#/info4], [debit4], [checknum]=[item#/info5], [debit5],
[checknum]=[item#/info6], [debit6], [checknum]=[item#/info7], [debit7],
[checknum]=[item#/info8], [debit8], [checknum]=[item#/info9], [debit9],
[checknum]=[item#/info10], [debit10], [checknum]=[item#/info11],
[debit11],
[checknum]=[item#/info12], [debit12]) AS dbt,
Switch([checknum]=[item#/info1], [credit1], [checknum]=[item#/info2],
[credit2], [checknum]=[item#/info3], [credit3], [checknum]=[item#/info4],
[credit4], [checknum]=[item#/info5], [credit5], [checknum]=[item#/info6],
[credit6], [checknum]=[item#/info7], [credit7], [checknum]=[item#/info8],
[credit8], [checknum]=[item#/info9], [credit9], [checknum]=[item#/info10],
[credit10], [checknum]=[item#/info11], [credit11],
[checknum]=[item#/info12],
[credit12]) AS crdt
FROM ([GROUP TAX INFO] INNER JOIN [GROUP ACCOUNTS RECEIVABLE] ON [GROUP
TAX
INFO].[ADVICE#] = [GROUP ACCOUNTS RECEIVABLE].[ADVICE#]) INNER JOIN
[PHYSICIAN GROUP] ON [GROUP TAX INFO].[GROUP NUMBER] = [PHYSICIAN
GROUP].[GROUP NUMBER]
WHERE ((([GROUP ACCOUNTS RECEIVABLE].[DT POSTED1])>[Enter Beginning Date]
And ([GROUP ACCOUNTS RECEIVABLE].[DT POSTED1])<[Enter End Date])) OR
((([GROUP ACCOUNTS RECEIVABLE].[DT POSTED2])>[Enter Beginning Date] And
([GROUP ACCOUNTS RECEIVABLE].[DT POSTED2])<[Enter End Date])) OR ((([GROUP
ACCOUNTS RECEIVABLE].[DT POSTED3])>[Enter Beginning Date] And ([GROUP
ACCOUNTS RECEIVABLE].[DT POSTED3])<[Enter End Date])) OR ((([GROUP
ACCOUNTS
RECEIVABLE].[DT POSTED4])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED4])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED5])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED5])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED6])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED6])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED7])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED7])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED8])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED8])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED9])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED9])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED10])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED10])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED11])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED11])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED12])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED12])<[Enter End Date]));

so after the results are off because if date posted 1 meets the criteria.
it
doesn't check to see if date posting2 meets the criteria.

Please help
Click to expand...
 
M

Mykas_Robi

Thank you very much Allen, This is a mdb that I inherited and have put off
redesigning. Looks like I will have to move the redesign up on my priority
list because it is seriously hampering my efforts to pull design reports.

Thanks again.

I think i need help with the redesign though.

Allen Browne said:
The problem is a classic example of why you need a relational design.

In a relational database, you do not have many repeating columns in the one
table (such as [DT POSTED4], [DT POSTED5], [DT POSTED5], and so on.)
Instead, you create a related table where there can be many related records
for one record in this table, each one with its own posting date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mykas_Robi said:
I have a table where each record consists of a series of posting dates,
posting items and amounts. I need to display those posting items and
amounts for each record that meeting a given date criteria. I am able to
use
the switch function to but it will only display the first occurrence where
the posting date meets the criteria.k I know the switch returns the first
occurrence; how do I get for a record the all occurrence where the posting
date meeting the criteria.


query

SELECT [Enter Beginning Date] AS Expr2, [Enter End Date] AS expr3, [GROUP
ACCOUNTS RECEIVABLE].[ADVICE#], [GROUP ACCOUNTS RECEIVABLE].LNAME,
[PHYSICIAN
GROUP].[GROUP NAME], Switch(([dt posted1]>Expr2 And [dt
posted1]<Expr3),[GROUP ACCOUNTS RECEIVABLE].[ITEM#/INFO1], ([dt
posted2]>[Enter Beginning Date] and [dt posted2]<[Enter End Date]),[Group
Accounts receivable].[item#/info2], ([dt posted3]>[Enter Beginning Date]
and
[dt posted3]<[Enter End Date]), [Group Accounts receivable].[item#/info3],
([dt posted4]>[Enter Beginning Date] and [dt posted4]<[Enter End
Date]),[Group Accounts receivable].[item#/info4], ([dt posted5]>[Enter
Beginning Date] and [dt posted5]<[Enter End Date]),[Group Accounts
receivable].[item#/info5], ([dt posted6]>[Enter Beginning Date] and [dt
posted6]<[Enter End Date]),[Group Accounts receivable].[item#/info6], ([dt
posted7]>[Enter Beginning Date] and [dt posted7]<[Enter End Date]),[Group
Accounts receivable].[item#/info7], ([dt posted8]>[Enter Beginning Date]
and
[dt posted8]<[Enter End Date]),[Group Accounts receivable].[item#/info8],
([dt posted9]>[Enter Beginning Date] and [dt posted9]<[Enter End
Date]),[Group Accounts receivable].[item#/info9], ([dt posted10]>[Enter
Beginning Date] and [dt posted10]<[Enter End Date]),[Group Accounts
receivable].[item#/info10], ([dt posted11]>[Enter Beginning Date] and [dt
posted11]<[Enter End Date]),[Group Accounts receivable].[item#/info11],
([dt
posted12]>[Enter Beginning Date] and [dt posted12]<[Enter End
Date]),[Group
Accounts receivable].[item#/info12]) AS checknum, [Group Accounts
Receivable].[fname], Switch([checknum]=[item#/info1], [debit1],
[checknum]=[item#/info2], [debit2], [checknum]=[item#/info3], [debit3],
[checknum]=[item#/info4], [debit4], [checknum]=[item#/info5], [debit5],
[checknum]=[item#/info6], [debit6], [checknum]=[item#/info7], [debit7],
[checknum]=[item#/info8], [debit8], [checknum]=[item#/info9], [debit9],
[checknum]=[item#/info10], [debit10], [checknum]=[item#/info11],
[debit11],
[checknum]=[item#/info12], [debit12]) AS dbt,
Switch([checknum]=[item#/info1], [credit1], [checknum]=[item#/info2],
[credit2], [checknum]=[item#/info3], [credit3], [checknum]=[item#/info4],
[credit4], [checknum]=[item#/info5], [credit5], [checknum]=[item#/info6],
[credit6], [checknum]=[item#/info7], [credit7], [checknum]=[item#/info8],
[credit8], [checknum]=[item#/info9], [credit9], [checknum]=[item#/info10],
[credit10], [checknum]=[item#/info11], [credit11],
[checknum]=[item#/info12],
[credit12]) AS crdt
FROM ([GROUP TAX INFO] INNER JOIN [GROUP ACCOUNTS RECEIVABLE] ON [GROUP
TAX
INFO].[ADVICE#] = [GROUP ACCOUNTS RECEIVABLE].[ADVICE#]) INNER JOIN
[PHYSICIAN GROUP] ON [GROUP TAX INFO].[GROUP NUMBER] = [PHYSICIAN
GROUP].[GROUP NUMBER]
WHERE ((([GROUP ACCOUNTS RECEIVABLE].[DT POSTED1])>[Enter Beginning Date]
And ([GROUP ACCOUNTS RECEIVABLE].[DT POSTED1])<[Enter End Date])) OR
((([GROUP ACCOUNTS RECEIVABLE].[DT POSTED2])>[Enter Beginning Date] And
([GROUP ACCOUNTS RECEIVABLE].[DT POSTED2])<[Enter End Date])) OR ((([GROUP
ACCOUNTS RECEIVABLE].[DT POSTED3])>[Enter Beginning Date] And ([GROUP
ACCOUNTS RECEIVABLE].[DT POSTED3])<[Enter End Date])) OR ((([GROUP
ACCOUNTS
RECEIVABLE].[DT POSTED4])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED4])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED5])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED5])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED6])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED6])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED7])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED7])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED8])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED8])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED9])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED9])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED10])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED10])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED11])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED11])<[Enter End Date])) OR ((([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED12])>[Enter Beginning Date] And ([GROUP ACCOUNTS
RECEIVABLE].[DT POSTED12])<[Enter End Date]));

so after the results are off because if date posted 1 meets the criteria.
it
doesn't check to see if date posting2 meets the criteria.

Please help
Click to expand...
Click to expand...
 
A

Allen Browne

Mykas_Robi said:
I think i need help with the redesign though.
Click to expand...

Okay, design is a big topic.

For starting form scratch, here's a couple of basic examples:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
and:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

Crystal (MS Access MVP) has a series of tutorial PDFs here:
http://allenbrowne.com/casu-22.html
The 3rd one is on normalization.

Jeff Conrad (Microsoft employee) has a bunch more links on normalization:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

When you have a specific question, you are very welcome to post here or in
the tablesdbdesign group.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reports ask for date range 2 times 2

Top