T
tektrader78
hi everyone,
Ive got a problem in access after migrating data from excel sheets
that were in a flat format.
I have a table called tblstatus that has over 10 status and counting.
Another table that has each account number having 10 status fields and
a respective date column.
The last table of course is for general information on the acount.
I would like to know how to setup a qry or how to run a report that
can pull the current status of any given account if say they are on
the third status, say pending.
When i pull a status it drags all the accounts and its difficult to
know the current status unlike the old method.
any input will be greatly appreciated.
Old database format:
| acct# | status | newdate | pendingdate | paiddate |etc
0001 new 1/1/03 1/1/03 1/1/03
0002 pending 1/1/03 1/1/03 1/1/03
0003 paid 1/1/03 1/1/03 1/1/03
In the old format, the status was manually changed to its current
status.
new format
tblstatus
code | statusname
01 new
02 pending
03 paid
tblaccountstatus
tblstatusdate
accountnumber | statusname | statusdate
123 new 01/12/99
123 pending 01/15/99
123 paid 01/17/99
234 new 01/12/99
234 pending 01/15/99
234 paid 01/17/99
345 new 01/12/99
345 pending 01/15/99
345 paid
Basically would like to use a qry to pull all the accounts in paid.
ps: in the very basic example above 123 and 234 should be returned as paid.
Accounts in pending would pull only 345.
I also explored using the tblstatus to make a sequence but to no avail.
Thanks,
Ive got a problem in access after migrating data from excel sheets
that were in a flat format.
I have a table called tblstatus that has over 10 status and counting.
Another table that has each account number having 10 status fields and
a respective date column.
The last table of course is for general information on the acount.
I would like to know how to setup a qry or how to run a report that
can pull the current status of any given account if say they are on
the third status, say pending.
When i pull a status it drags all the accounts and its difficult to
know the current status unlike the old method.
any input will be greatly appreciated.
Old database format:
| acct# | status | newdate | pendingdate | paiddate |etc
0001 new 1/1/03 1/1/03 1/1/03
0002 pending 1/1/03 1/1/03 1/1/03
0003 paid 1/1/03 1/1/03 1/1/03
In the old format, the status was manually changed to its current
status.
new format
tblstatus
code | statusname
01 new
02 pending
03 paid
tblaccountstatus
tblstatusdate
accountnumber | statusname | statusdate
123 new 01/12/99
123 pending 01/15/99
123 paid 01/17/99
234 new 01/12/99
234 pending 01/15/99
234 paid 01/17/99
345 new 01/12/99
345 pending 01/15/99
345 paid
Basically would like to use a qry to pull all the accounts in paid.
ps: in the very basic example above 123 and 234 should be returned as paid.
Accounts in pending would pull only 345.
I also explored using the tblstatus to make a sequence but to no avail.
Thanks,