If conditions

[Scott]

Hello,

I could use some help with "if" conditions as I need to separate dollars by
the first character within an account number.

Example-Table:
Field 1 Field 2
(Account) (Amount)
500-00 100
550-00 75
600-00 50
625-00 325
700-00 200
800-00 300

For my query, I need to put 500,600,700,and 800 series accounts into
separate fields. With an "if" condition, I need to say "If account begins
with 5, pull the amount or else make the amount field 0." My output would
look as such.

Query from table above:
Field 1 (500s) Field 2 (600s) Field 3 (700s) Field 4 (800s)
Field 5 (acct)
100 0 0
0 500-00
75 0 0
0 550-00
0 50 0
0 600-00
0 325 0
0 625-00
0 0 200
0 700-00
0 0 0
300 800-00


Thanks for your help.

Scott

 

[John Spencer]

That looks like a crosstab query. The SQL for that would probably look like
the following

TRANSFORM First(Amount)
SELECT Account
FROM YourTable
GROUP BY Account
PIVOT Left(Account,1) & "00"
ORDER BY Account

In the query grid
-- New query
-- Select your table
-- Add the Amount field 1 times and the Account field twice
-- Select Query: Crosstab Query from the menu
-- Under one account field set the Total to Group By and Crosstab to Row Heading
-- Under Amount: Total = First (or Sum) Crosstab = Value
-- In the second account field Total = Group By and Crosstab = Column Heading
-- Modify the Field to read Left([Account],1) & "00"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County