Connecting Queries

[josef_vp]

Hi there, I'm currently writing an accounting system and i got stuck with this section of trying to produce the account name in a query.

i have four tables

tblPurchase
PurchaseID -Number

tblAccounts
AccountID - Number
AccountName - Text, usually has names like asset, cost of sales, expense, telephone, electricity, etc...

tblItems
ItemID, Number
AssetNum (tblAccounts.AccountName foreign key1), Number
ExpenseNum(tblAccounts.AccountName foreign key2), Number
IncomeNum(tblAccounts.AccountName foreign key3), Number

tblPurchaseLines
PurchaseNum (foreign key for tblPurchase.PurchaseID), Number
ItemNum (foreign key for tblItems.ItemID), Number




My question is how can i generate the query with the following fields:

PurchaseID
ItemID
AccountName of AssetNum
AccountName of ExpenseNum
AccountName of IncomeNum

I am aware that the query would produce three PurchaseID's for every ItemID it would encounter for every PurchaseLine.

Please point me in the right direction.

 

[Bilbo_Baggins_Esq]

Could you post a sample of the database in zipped format?

 

[ajetrumpet]

My question is how can i generate the query with the following fields:

PurchaseID
ItemID
AccountName of AssetNum
AccountName of ExpenseNum
AccountName of IncomeNum

Joseph, what would you like to do with the query?? Are you trying to make a new table with just these fields?? This sounds like a simple SELECT(), FROM() statement. Are ALL of your tables properly related as well??

tblItems
ItemID, Number
AssetNum (tblAccounts.AccountName foreign key1), Number
ExpenseNum(tblAccounts.AccountName foreign key2), Number
IncomeNum(tblAccounts.AccountName foreign key3), Number

You should probably have a simple one-many relationship set up here through your Account Name PK in (tblAccounts). Do you have this table set up such like this...
ITEMID, NUMBER. ACCOUNTTYPE, ACCOUNTNAME (field names)??