Union Query question

[Slab_Rankle]

Hey all,

I'm having an issue merging two queries of mine. Basically one shows a clients income and the other shows a clients expenditure. The tables in both queries consist of an Account Number, a Type and the AmountPaid. Usually there are more entries in the Expenditure query, but when I join them the IncomeType duplicates itself to the amount of Expenditure records in the query.

So, for example, if I have 4 Expenditure entries and 1 Income entry, the query shows 4 expenditure entries and 4 income entries. Now, I think what I need to do is a union query, but I'm unsure on how to structure it. Here's the SQL for both of the queries I mentioned earlier on:

Income Query:

SELECT IncomePaid.[Account Number], Income.IncomeType, IncomePaid.AmountPaid
FROM Income INNER JOIN IncomePaid ON Income.IncomeID = IncomePaid.IncomeID;

Expenditure Query:

SELECT ExpendPaid.[Account Number], Expenditure.ExpendType, ExpendPaid.ExpenditureAmount
FROM Expenditure INNER JOIN ExpendPaid ON Expenditure.ExpendID = ExpendPaid.ExpendID;

I want to do this so I can display both the Expenditure and Income on the same report as when I use a sub report it only lets me display the Income in the footer which is obviously right at the bottom of the page.If anyone could help that'd be great!!

 

[RainLover]

Rather than have two Tables, (One for Expenditure and another for Income) which I am assuming you have. I would have just the One table called Transactions and Tag each entry with Income or Expenditure.

As I am not sure of your structure this is just a suggestion. Hope it helps.

 

[Slab_Rankle]

Hmm, I don't think I can do that, I actually have 4 tables (2 for income and 2 for expenditure), the first table is a list of different IncomeTypes. Each IncomeType has an IncomeID. I've named this table Income.

Now, in the next table (which I've called IncomePaid) I have the AccountNumber, the IncomeID and the AmountPaid. The IncomeID is linked to the Income table I previously mentioned, so basically the user selects one oft the IncomeType's from a dropdown. This is exactly the same for the Expenditure tables, both are set up in the same way and function the same way.

Does that help clarify everything? I'm still a bit unsure on how to make the report look professional without having all the income stuff appear in the footer of the document.

 

[RainLover]

It appears as though the setup of your Tables could be improved.

Have you read any articles on Normalisation.

Do you think you could post a copy of your Database with just the tables and some dummy data.

Please convert to 2003 before posting as I do not have later versions.

 

[Slab_Rankle]

Yep I'll have to duplicate the back end and put some dummy data in so I'll upload it when that's been done!

 

[Slab_Rankle]

Ah, well, I can save the back end as 2003 but when I try to convert the other half it says it cannot be converted because it uses features reliant on that version. Is there anything you can do with just the back end?

 

[RainLover]

The back end is all I need at the moment.

 

[Slab_Rankle]

Here's the database, some of the earlier stuff I developed was when I was a bit of a newbie so I realise there may be some techniques used that are frowned upon! Anyway, thanks for helping .

 

[RainLover]

Just a couple of tips before we get too involved.

Avoid spsces in the names of your fields.
Do not use Lookups or value lists at the table level. Use these at the form level.

You have used ****ID as the name of the primary key. This is a good practice and you should consider using this as a standard.

The Foregin key should have the same name of the primary key.

To help with your problem, I believe it should be Normalised a bit better than what you have.

Let's look at the following Tables.

BasicDetails
Creditors
CreditorInfo
CreditorBranch.

In all of these Tables you have the "ADDRESS". Is this the same in each table. If so then this is repeating data know as "Redundant Data". It is good practice to record information once only.

Can you please explain the purpose of these group of Tables.

Creditors
Accounts
UnsecuredDebts
Sales
Income
Expenditure

I believe you have too many tables but cannot say for sure until I know more.

 

[Slab_Rankle]

I know about the spaces thing now, when I first started I didn’t and instead of going back and changing it all I decided to jus t leave it but apply that knowledge to anything new I created. Anyway, in answer to your question:


The Address fields on BasicDetails are the address details of the client.


The Address fields on CreditorInfo are the address details of the Creditor.


The Address fields on CreditorsBranch are the address details of a branch of a Creditor.



The table Creditors actually looks up the address from the CreditorInfo and displays them to the user on a form. It uses a DLookUp to do this. The rest of the tables do the following:



Creditors: This displays all of a client’s creditors, including fields such as their account numbers, current payments, amount owed etc.



Accounts: The Accounts table shows information about the accounts a client has with a creditor. It displays information such as their payment date history, how much they have paid, the type of transaction etc.



UnsecuredDebts: This shows how much a client owes to a particular creditor. It shows things such as how much they owe a creditor, how much their current monthly payments are and what type of debt each entry is (such as a Loan, a store card, an overdraft etc).



Income: This simply displays each type of Income. When a user is on the Income/Expenditure tab they can click a dropdown under the title of Income Type and select one from the list and enter the amount of income next to it.



Expenditure: Same as Income except it has different types of Expenditures.



Does that help at all?

 

[RainLover]

In my first reply, I suggested that you have One Table not Two. I still think that that suggestion is still valid.

I am of the opinion that you could combine ExpendPaid and IncomePaid into the one table. You could have another table as a lookup to describe the type of Income or Expence.

Back to you.

 

[Slab_Rankle]

O.K, got it, so, I've merged IncomePaid & ExpendPaid, and I've merged the Expenditure table with the Income table. The Income table now has all the IncomeTypes AND all the ExpendTypes, and the IncomeType table now has all the Income/Expenditure details that have been entered by the user. Have I done that properly? I can upload another version if that will help?

 

[RainLover]

Sounds good to me.

You should be able to create the report you need?

If not what problems are you having?

 

[Slab_Rankle]

Hmm, when I do the report it only shows the first entry of income and the first entry of the expenditure for each client. For example, if client A has 2 Income types and 4 Expenditure types, only the first one of each is shown, then beneath that it shows client B's income/expenditure. Could this be a relationship problem? When I try to do a query it does the same thing.

 

[RainLover]

For example, if client A has 2 Income types and 4 Expenditure types, only the first one of each is shown,

Are you wanting to show all Types even if there are no entries against some.

Please post the SQL of your Query.


Edit

Create the query in the Back End and then post the revised Back End.

 

[RainLover]

Could you create the query in the Back End and then post a copy.

 

[Slab_Rankle]

I only want to show the types that a particular client has, many clients usually just have 1 source of income but very often have multiple types of expenditure. Here's the SQL code for my query:

SELECT IncomePaid.[Account Number], Income.IncomeType, IncomePaid.AmountPaid, Income.ExpendType, IncomePaid.ExpenditureAmount
FROM Income INNER JOIN IncomePaid ON Income.IncomeID = IncomePaid.IncomeID;

 

[Slab_Rankle]

Sure thing! Here it is. Thank you!

 

[RainLover]

You are joining on Income and some of the entries are blank.

IncomeID and ExpendID should be Combined as one field.

Then use a Lookup to another Table to Tag the TYPE. This Type would not only be Income or Expend but also the Income Type or the Expend Type.

This Lookup Table would have three Fields.

ID
Expend/Income
Type.

 

[Slab_Rankle]

Hmm, I'm a bit confused by the last bit. The Expenditure and Income both have their own types, by having one field 'Type' wouldn't that mean they'll share whatever is stored in that? So for example someone would be able to use an Income type for an Expenditure? E.g. Rent as a type of Income? Sorry...I'm just struggling to understand the last bit you explained.