Union Duplicate ID

[ClaraBarton]

I have a combo that picks from one of 2 tables so I use a Union query to combine the choices. However, the primary key on both tables creates duplicate ID's in the bound column of the combo box. How do I know which table is chosen?

 

[theDBguy]

If there are duplicate values in the bound column of a combobox, the first item is "always" chosen by default. To know which table each row belongs, include that info in your UNION query.

 

[mike60smart]

I have a combo that picks from one of 2 tables so I use a Union query to combine the choices. However, the primary key on both tables creates duplicate ID's in the bound column of the combo box. How do I know which table is chosen?

Hi Clara

Very unusual to have a 2 Table Combo when you want to retrieve values from 1 specific table.

 

[plog]

UNIONs are always a red flag of a improperly set up databases. Add to it this specific use and I'm 99% certain you haven't structured your tables properly.

If these are similar choices, why are they in different tables? Can you give us a bigger picture view with some better specifics? What are these choices exactly? What are the 2 tables that you are using in that UNION?

 

[ClaraBarton]

I can but you'll yell at me. Quicken uses categories to do account transfers. For instance, if i take $200 out of checking and pay a bill and keep the rest as cash, $20 is an expense and $180 is an account transfer. The transfer creates another transaction (out of checking, into cash), so no category. Please don't tell me Quicken can do a better job. I know that. This is to add to my invoicing db.

 

[plog]

Not too get too profound, but 'No Category' is a category. Of course I still have no insight into how your database works or is structured.

I've got a vague notion that you are choosing categories for things, but I still don't understand why you have categories in two separate tables you need to combine.

 

[ClaraBarton]

Categories are in one table. Accounts are in one table. Do you want your money to go to an expense category? or to an account (transfer)?

 

[CJ_London]

are you attempting to update quicken from your db or the other way round?

 

[ClaraBarton]

Oh neither. I'm just creating a quick expense section for my invoicing. I'm not trying to compete with Intuit but I've been an accountant for years and have used these products so this is the direction my mind goes.

 

[ClaraBarton]

If anyone's interested here's what I did. Works! Alias category ID's with a C in front of the ID and Account ID's with an A. Then trim the letter out when you work with it.

 

[Gasman]

Quicken uses a qdf file. Just one file and I expect just a field to identify the type of transaction?

Whenever I have taken cash out in quicken it just reduces my balance?
Same as if I paid a bill.?

 

[Pat Hartman]

If anyone's interested here's what I did. Works! Alias category ID's with a C in front of the ID and Account ID's with an A. Then trim the letter out when you work with it.

This is a really bad idea and it actually doesn't work. You'll find out eventually. The idea of a combo is that the RowSource has a UNIQUE identifier. When you choose an item, the FK is copied from the RowSource and placed in the bound control. OK. You can also save the source field. But when you open the form again, the bound control ALWAYS points to the first instance of the duplicate identifier.

You should probably fix the schema and not use separate tables for something that should be stored in a single table.