Temp Table or something else

[stubnski]

Hi all,
I would appreciate if someone can point me in the right direction with my problem.
This is for a financial application, Access 2000

I have four tables - Application, Approved, Funded, and Canceled. Every new record starts out by being added to the Application table, from there it is either Approved or Canceled(Records can be put into the Canceled table at anytime). If it is Approved then it can go to the Funded table or it can be Canceled. Records can NOT go from Application to Funded, and once a record is Canceled it cannot be undone(unless done by mistake, but that's another problem)
Application table PK is other tables FK.
For each of these tables, one account number can have multiple entries with different data (all have primary keys)
Application Table -
Ex. PK # Account # F Name L Name Amount
1 1234 John Doe $2
2 1234 John Doe $5
3 433 Jane Doe $7
4 567 James Doe $10

Approved Table
PK FK # Account # F Name L Name Amount
1 1 1234 John Doe $2
2 2 1234 John Doe $5
3 3 433 Jane Doe $7

Funded Table
PK FK # Account # F Name L Name Amount
2 3 433 Jane Doe $7

Canceled Table
PK FK # Account # F Name L Name Amount
1 2 1234 John Doe $5
2 4 567 James Doe $10

What I want to do is to use a list box or a combo box to display all records based on a query (Account #). So John Doe would display twice with what his/her status is(What Table record is being pulled from)
EX - Account # F Name L Name Amount Status
1234 John Doe $2 Approved
1234 John Doe $5 Canceled

One record is then selected and it's information is displayed in textbox's so it can be modified

I hope I explained everything clearly.

TIA
Stubnski

 

[GingerR]

Wow-you are going to a lot of extra effort. You are also storing the same information in muliple places, which I would say is a no-no.

How about having one table with the info, and a field called "STATUS" which holds the status? Then a table called Status:

StatusID Status
1 Initiated
2 Cancelled
3 Funded

Then you can select the Status from a combo box to fill the field. Then all of your data is in one table and you have no more problems.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[lameid]

What you want is called a Union query. A general example is below. You would have to add the other tables with more select statements.

Select [Account #], [F Name], [L Name], [Amount], "Application" AS Status
From Application
Where [Account #] = 1234

Union All

Select [Account #], [F Name], [L Name], [Amount], "Approved" AS Status
From Approved
Where [Account #] = 1234

 

[stubnski]

Hi,
Thanks to the both of you for your quick replies.

GingerR -
The reason I can't have all of the information in one table is because that, with the exception of the name and account #, all other information can be changed...ie - different dates for Application, Approved, Funded, and Canceled. There are about 5 other fields that can be changed.

Lameid -
What are you suggesting with the Union query? Returning the value to the combo or list box? If that is what you are suggesting how would I go about doing that?

Thanks again for the replies

Stubnski

 

[lameid]

If you open a query, switch to SQL view, paste the SQL in and run the query you will get results (Union queries are only available in SQL View). You will also have to union in the other table(s) and maybe take out application depending on what you really want.

Then you make the Row Source for the combo box the query.

The catch with union queries is that each select statement must have the same number of columns and the matching columns must have the same datatypes but not necessarily the same field/column names (keep it simple and make them the same).

Following the above paragraph, you could make a regular query for each table complete with criteria etc. Then you can write a generic Union query to select all the fields. This allows you to manage more in the QBE as opposed to SQL view which is much easier for beginners and some of us advanced users...


Select *
From qry1

Union All

Select *
From qry2

GingerR is right though, you should be thinking more about data normalization. Check out

http://www.datamodel.org/NormalizationRules.html

In my defense, it has been a bad day.

In your case Account, and name should only be in the application table if it truely cannot change. You might also see benefit from putting all the stati (statuses?) in one table to avoid nasty queries that are inherently slow like union queries.

 

[stubnski]

Thanks Lameid for the response and advice.

The way I have my tables setup is as follows -

Application Table (Main Table) -
LN_id(PK), Account#, Fname, Lname, App_date, amount, officer, location, type
(Officer, Location, and Type are each in their own seperate tables(4~6 records per table) and are selected from a drop down, rest are input boxes)

Approved and Canceled tables are Identical...except for field names
LN_id(FK), A or C _ID(PK), A or C _Date, Amount, Officer, Location, Type

Funded Table
LN_id(FK), F_ID(PK), F_Date, Amount, Officer, Location, Type, Sold, Purchased, Rate

Every table is linked to the Application table because the search criteria will be the account #. Would it still be a good idea to put all of these fields into one table? All information except the Name and Account # can be changed.

If I use the SQL view, how do I base the query on the entered Account #?

Sorry for being a pain in the rear!

Stubnski

 

[stubnski]

Nevermind my last question - If I use the SQL view, how do I base the query on the entered Account #?

I figured out how to do it. But I would still like your advice on either to use one table or use the multiple table setup like I have it.

Stubnski

 

[GingerR]

Please Normalize your tables!! Without further info, I'm just guessing at how your set up should be. But it looks like you have Accounts, each Account can have different Amounts (transactions?) and each of those Transactions can have one or more STATUS (Approved, Cancelled, etc). Right?

Also, it seems that Officer, Location and Type are related to a Transaction?

Looks like you should have this:
TableName: Accounts
Field: AccountID
Field: FName
Field: LName
{other data that is related to an Account}

Sample Data:
1234 John Doe
433 Jane Doe

TableName: AccountTransactions
Field: AccountID
Field: TransactionID (Autonumber)
Field: TransactionAmount
Field: Officer
Field: TransactionType
Field: Location
Field: Sold
Field: Purchased
Field: Rate

1234 1 $2 Johnny Jones Type? Seattle
1234 2 $5 Steve Arino Type? Eugene
433 3 $7 Pete Smith Type? Burbank

TableName: Status
Field: StatusID
Field: Status (1-Application, 2-Approved, 3-Cancelled, 4-Funded)

TableName: AccountTransactionStatus
Field: TransactionID (from table AccountTransactions)
Field: StatusID (from table Status)
Field: TransactionDate

1 1 1/1/06
1 2 2/1/06
2 1 1/15/06
2 2 2/28/06
2 3 3/1/06
3 1 4/1/06
3 2 4/2/06
3 4 4/15/06

So, you can enter Acct info ONE TIME, not putting FName, LName etc multiple times. A good rule to follow is this: If you start repeating the same data row after row after row, it's time for a new table!

And when you enter a new Transaction, write code that automatically adds a row to the table "AccountTransactionStatus" with Status = 1 (Application) and the current day's date. You could easily do this with an APPEND query and just run code behind an After Update event of a form. Then every "Transaction" will automatically have an "Applied/Application" date assigned to it. Then you can have a subform that shows all of the Status' for a certain Account/Transaction. You can keep adding Stati (?) APPROVED to FUNDED, or whatever. Each with the current date. This way, as you can see, all of the status' and dates are in one table and everything is "NORMALIZED"!! If my terms or understanding of your business process is slightly wrong, please try to fiddle around with this and understand it on your own, then write back and let us know how you do!

Ginger

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html