Union query and form coding

[davea300]

Hi
Im looking for advice with a union query and search facility I'm trying to create. I have 2 tables, (will just call them tbl1 and tbl2 for this example) each with a column AppNumber. The AppNumber may appear in both tables or individually in either one.

I want to be able to show, or flag, on a continuous form which Appnumber's appear on tbl1, tbl2 and on both. This is so the user can click a button and bring up another form with more data relevant to the AppNumber.

I have written a union query to show all results from both tbl1 and tbl2 which contains 4 fields AppNumber, DateApp, Surname, Forename but I can't tell from the query results what AppNumber's appear on each table.

Here's what I would like to show on the continuous form:

AppNumber DateApp Surname Forename tbl1 tbl2 Both
12345 11/12/2012 Simpson Homer Yes No No
56565 01/04/2013 Stallone Sly No Yes No
89788 03/03/2012 Rambo John Yes Yes Yes
89745 02/02/2012 Solo Han Yes No No

The yes and No flags would be coded as buttons so they could open other forms with more data. Should I go down the route of coding the buttons individually?

Any suggestions?

 

[Steve C]

If it’s the SAME AppNumber consider if there might be an AppNumber Table above and in a one-to-many relationship with both tbl1 &tbl2.

In your example, if AppNumber 12345 can relate to BOTH Simpson Homer AND Stalon Sly? I think you need an AppNumber Table to record AppNumbers

Yes/No boxes are troublesome. They don’t allow for a third option (nothing) because in access nothing is not the same as zero or no in a Y/N box. Instead, you might consider a text box where only Yes or No can be entered. The advantage is that Text boxes are fine with nothing in them in a way which Yes/No boxes just aren’t.

If you want to keep your Yes/No boxes be sure to set the default to either state when the record is created. Then there won’t be any Nulls to worry about.

 

[davea300]

If it’s the SAME AppNumber consider if there might be an AppNumber Table above and in a one-to-many relationship with both tbl1 &tbl2.

In your example, if AppNumber 12345 can relate to BOTH Simpson Homer AND Stalon Sly? I think you need an AppNumber Table to record AppNumbers

Yes/No boxes are troublesome. They don’t allow for a third option (nothing) because in access nothing is not the same as zero or no in a Y/N box. Instead, you might consider a text box where only Yes or No can be entered. The advantage is that Text boxes are fine with nothing in them in a way which Yes/No boxes just aren’t.

If you want to keep your Yes/No boxes be sure to set the default to either state when the record is created. Then there won’t be any Nulls to worry about.

Hi

Each application number in my system only relates to one person. What I have now is a union query showing all the records from 2 tables. Each application number can appear in one or both tables but always relates to the same person (each table has different data associated with the application).

So what I need to show is a union query of both tables showing all applications but highlight each one to show whether it appears in tbl1, tbl2 or both. Something like this:


tbl1___App___tbl2
........ 12345 12345
56565 56565
89788 89788 89788
89745 89745

which shows 12345 as being in tbl2 and 89788 as being in both tbl1 and tbl2. The column app is generated from my union query.

I've tried linking my union query via query builder in access to both tbl1 and tbl2 but it only shows those records that appear in both tables.