Union Query question

[RainLover]

ID Source (Income/Expend) Type

1001 Income Rent

This would be a typical Record in your Table. You can list all the Income Types and All the Expend types in the one Table.

Later you can Filter by income or Expend which would only show the Types attached to the filtered Income or Expend.

 

[Slab_Rankle]

So…let me get this right, I have one table (say I’ll call this Lookup) with the following fields:



ID (AutoNumber)
Source (Text - A list that has Income/Expenditure as values)
Type (Text – a list of all the different types of income/expenditure)


Then I need to make another table (that merges the previously used IncomeID/ExpendID). This would have the following fields:



Inc/ExpID (AutoNumber)
Source (Looks up source from other table)
AmountPaid (Currency)
Type (Looks up type from other table)



Am I on the right track?

 

[RainLover]

Close. Try this.

Inc/ExpID (AutoNumber)
Type (Text – a list of all the different types of income/expenditure)
AmountPaid (Currency)

No need for a separate Field for source because when you select type you are also selecting source..

If you can't solve this then I will create a Demo for you tomorrow.

 

[Slab_Rankle]

Sorry for the late reply, I think I'm having a few problems with my second table, would you be able to create a quick demo for me just so I can see how it works? My current tables are:

Lookup:

Inc/ExpID (Autonumber)
Source (Text - contains a list of "Income" and "Expenditure")
Type (A list of all the different types of Income/Expenditures)

& this is my second table, Inc/ExpPaid:

Inc/ExpID (AutoNumber)
AccountNumber (Number)
Type (Text – a list of all the different types of income/expenditure)
AmountPaid (Currency)

Do I need another instance of Type? Or will that just lookup from the other table?

 

[RainLover]

Sorry for this.
I actually had to do some work. How terrible.

Will do something for you tomorrow.

 

[Slab_Rankle]

No need to apologise! Thank you for taking the time to help

 

[RainLover]

The attached only Demonstrates the use of Comboxes to avoid using Union Queries. I hope this helps.

Open Form "Demo Form" from the Intro Form.

Click on the field TransactionFK (FK for Foregin Key) and you will see a list. So just scroll down and choose the description that you need.

 

[Slab_Rankle]

That's brilliant Rain, thank you so much, I understand it perfectly now! I do, however, have one more question! As I'm changing the layout of my tables, how do I get all the data that I currently have in these tables into the tables I've just created? Is it just a case of copying and pasting the information into the right fields? Or is there a faster way to do it?

 

[RainLover]

You can use an Append Query.

Search help if you need.

A bit of friendly advise. Make sure your Table design is correct. If not you will be forced to use more and more unnecessary complicated code.

Read up on normalisation. Very Important.

Good luck with the project.

 

[Slab_Rankle]

Thanks for all your help Rain, the demo you did for me has definitely helped! I'll be sure to look up on the append query and the normalisation stuff, when I first started the project I wasn't the best at Access so a lot of poor design decisions were made. This has helped me restructure the tables with minimal damage and shown me how to structure them properly! Thanks again!

 

[RainLover]

Most Welcome.

Glad I could help.