Re: Switch, IIf or ?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mykas_Robi" <MykasRobi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:99C8B47C-D03D-4024-A8D0-0EBC08250810@xxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • Response from Southern Electric
    ... Re my earlier posting on here: ... since our website was redesigned ... operating systems including Mac OS, ... are currently undergoing a complete redesign and as we introduce the ...
    (uk.comp.os.linux)
  • Re: RealPlayer - any free
    ... you can see my first post as an MVP was made on 1 Jul 97 in a W95 newsgroup. ... complain about it nor chastise people for bottom posting. ... poster is a top poster, I immediately see what that person has to say ... criteria requested, by the OP, ...
    (microsoft.public.windowsxp.general)
  • Re: Switch, IIf or ?
    ... The problem is a classic example of why you need a relational design. ... 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. ... amounts for each record that meeting a given date criteria. ... FROM (INNER JOIN [GROUP ACCOUNTS RECEIVABLE] ON [GROUP TAX ...
    (microsoft.public.access.queries)
  • Re: best TV Monitor? 2nd attempt
    ...  I suggest you get a real newsreader, since Google ...
    (sci.electronics.repair)
  • Re: [opensuse] Second Try: Redesign of YaST Control Center
    ... redesign, which is really needed, I like to have the chance to ... rearrange the items using my criteria. ...
    (SuSE)