Union Query with division

[Silvio]

Hello, it would like to join two tables, Table1 and Table2, however when
joining table2 the items listed in that table need to be divided by 2. I
normally do this division in a query then join that query to the union query
with table1. I wonder if I can accomplish the same thing directly from the
union query only. Also, is there a way to rename a customer ID with the same
query? (See Customer 9 as example, changing to Customer 15)

Example:

Table1:
[Customer] [Hours]
Customer 1 10
Customer 3 5

Table2:
[Customer] [Hours]
Customer 9 6
Customer 10 5

Union Query result:
[Customer] [Hours]
Customer 1 10
Customer 3 5
Customer 15 3
Customer 10 2.5

Thank you,
Silvio

 

[Jeff Boyce]

Silvio

One approach to this might be to first create two queries, one for each
table, which generate the results you want (e.g., for the second table,
divided by 2).

Then you could create a union query something like:
SELECT * FROM YourQuery1
UNION
SELECT * FROM Your Query2

By the way, if you have two tables with identical structure (e.g.,
[Customer], [Hours]), you have ... a spreadsheet! In a well normalized
relational database, you would rarely ever use two tables with identical
structure.

If you will provide more specific description of what your current data
structure is like, folks here may be able to offer more specific
suggestions...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

Also, is there a way to rename a customer ID with the same query? (See
Customer 9 as example, changing to Customer 15)
Use a calculated field --
Customer_: IIF([Customer] = "Customer 9", "Customer 15", [Customer])

If you have lots to change then use a translation table --
Translation --
Old New
Customer 9 Customer 15
Customer 14 Customer 16
Customer 11 Customer 19
Use a calculated field --
Customer_: IIF([Customer] = [Translation].[Old], [Translation].[New],
[Customer])