Linking Tables

[hermannm]

Hi I have an access database I am trying to build but am running into issues. I want to have the following tables:



Bank Accounts - List of all my bank accounts
Spending - Table that holds monthly spending. The spending will be on credit card or may come from the bank account
Inflow - Table that records monthly income
Payments - Table that records payments to credit cards and which bank the amount came from

I then want to have a query that shows the balances in the various accounts. For example i want to link the Bank Spending, Inflow and Payments which should show me the balance in the account. However when I try and do this the records from the tables go into columns instead of a seperate row for each record.

Hope this makes sense.

Thanks,

Matt

[Micron]

maybe there is something here that will fit. There's about 16 bank related schemas under section 8
http://www.databaseanswers.org/data_models/

However when I try and do this the records from the tables go into columns instead of a seperate row for each record.

You could also post a pic of your relationships to give an idea of what you tried.

[CJ_London]

these three tables

Spending - Table that holds monthly spending. The spending will be on credit card or may come from the bank account
Inflow - Table that records monthly income
Payments - Table that records payments to credit cards and which bank the amount came from

should be one table with a column to indicate whether the record is a spend, inflow or payment

Or use a union query on your three tables to combine the three tables

[hermannm]

these three tables




should be one table with a column to indicate whether the record is a spend, inflow or payment

Or use a union query on your three tables to combine the three tables

Thanks. I was thinking the only way this works would be to have one table. I would prefer to have 3 tables, but i think i can make it work just using 1 table and then designing different reports to disaggregate the data to Inflow, Spending and payments.

Matt

[CJ_London]

but i think i can make it work just using 1 table

1 table is the proper way to do it. Keep the 3 tables if you prefer, but you will be making it much more difficult for yourself with the need for extra queries, forms and reports. e.g.

then designing different reports to disaggregate the data to Inflow, Spending and payments.

you can do this with one report using the transaction type as a filter - with 3 tables you would need to build 3 reports (4 if you also want a report of all transactions in date order)