How to avoid duplicate fields in a query?

[ariansman]

There are two tables. The first one is “Employee Expenses” ( EE), where “employee name”, “expense type” ( such as transport, print, advertisement), “amount” and “date” are recorded. The other table, is named “Employee Sales”( ES) and stores “employee name”, “sold product”, “quantity” and “date”.
A third table is needed, maybe by a query, to show data from both tables, to have expenses and sales for employees. I do not know what to do about the common fields. In the design view, shall I choose “date” and “Employee name” from both tables? How can I have one “date” field in the query which gets data from both table? I hope I am describing the situation clearly.
Thank you

 

[Pat Hartman]

With the exception of foreign keys which should always be selected only from the "many" side table, there should be no other duplicate columns. A date in one table is not the same as a date in another dispite the common name. When you create the query, select the columns you want from each table. If there are common names, disambiguate them.
select tbl1.Date as PurchaseDate, tbl2.Date as ReceiveDate From
tbl1 inner join tbl2;

PS - Date (month, name, year, etc) is a poor choice for a column name since it is the name of a function. It is best to avoid using function and property names as well as other reserved words as column names. When you get to VBA, you will have problems with those names. And while we're add it, don't use symbols or embedded spaces either. Just a-z, A-Z, 0-9, and the underscore.

 

[sparks80]

Hi,

Can I clarify something:

1) Are the expenses and sales in the two tables related (e.g. is there a corresponding sale for each expense and you are trying to link the tables by the common fields) OR
2) The sales and expenses are unrelated and you need to create a combined list of sales and expenses.

Sorry I may have missed something, so in the first case Pat Hartman's advice is on the money. In the second case you could join the two tables using a union query which I'm sure someone can help with!

 

[ariansman]

Hi,

Can I clarify something:

1) Are the expenses and sales in the two tables related (e.g. is there a corresponding sale for each expense and you are trying to link the tables by the common fields) OR
2) The sales and expenses are unrelated and you need to create a combined list of sales and expenses.

Sorry I may have missed something, so in the first case Pat Hartman's advice is on the money. In the second case you could join the two tables using a union query which I'm sure someone can help with!

Primary keys are already defined for each table: “expense ID” and “sales ID”. Moreover, the two tables are related to other tables appropriately. For example, the “employee name” field is a foreign key related to “employee details” table. However, the “Employee Expenses” table and “Employee Sales” are not related. It make sense that an employee has sold some specific product on 1/1/1012 while he has spent some money for some kind of “prints” on 5/1/2012. Expenses and sales are neither commensurate nor related. On the third table we want to see the sales and expenses of employees.

 

[ariansman]

With the exception of foreign keys which should always be selected only from the "many" side table, there should be no other duplicate columns. A date in one table is not the same as a date in another dispite the common name. When you create the query, select the columns you want from each table. If there are common names, disambiguate them.
select tbl1.Date as PurchaseDate, tbl2.Date as ReceiveDate From
tbl1 inner join tbl2;

PS - Date (month, name, year, etc) is a poor choice for a column name since it is the name of a function. It is best to avoid using function and property names as well as other reserved words as column names. When you get to VBA, you will have problems with those names. And while we're add it, don't use symbols or embedded spaces either. Just a-z, A-Z, 0-9, and the underscore.

For some items such as “date” we can make different names. However, I think it is still preferable to have one “date” field to show both types of activities. But this is not possible for all the items. For example we cannot have two kind of “employee names".

 

[sparks80]

So are you looking to create a table with four fields like this?

Employee Name, Date, Expense Type / Sold Product, Amount / Quantity
Mark, 1/1/2012, expense: travel, amount: 12.50
Jane, 1/1/2012, product: painting, quantity: 12

The SQL for the union query would be something like this:

SELECT EmployeeName, ExpenseDate As ExpenseOrSaleDate, ExpenseType as ExpenseTypeOrSoldProduct, ExpenseAmount As AmountOrQuantity From ExpenseTable
UNION ALL SELECT EmployeeName, SaleDate, SoldProduct, SoldQuantity FROM SalesTable
ORDER BY ExpenseOrSaleDate

 

[Pat Hartman]

As I look at this again, it appears that you are attempting to get data from two tables that are not related to each other in the same query. A straight join including thes two unrelated tables will result in a Cartesian Product which "duplicates" data and will not give you what you want. Someone suggested a union query which will accomplish your purpose but the union will need to do a full outer join and then the individual rows will need to be summed to get them into a single row. A simpler solution would be a report with two subreports. One to show sales and the other to show expenses.