Select Query 2

[MelF]

Just don't know how I should be doing this: I have a query including two tables, Orders (Prim.Key=OrderID) and EmployeeShipping (Prim. Key = OrderID). The Orders table contains an OrderDate field. The EmployeeShipping table contains an EmpOrderDate field. Since both primary keys are called OrderID, I can't add one OrderID field to the other table as a Foreign key, because Access won't let you have two fields in one table with the same name. What I need to get out of this query is all OrderDates (from Orders table) and all EmpOrderDates from EmployeeShipping table. Any suggestions? Thanks!!

 

[kor]

"SELECT Orders.OrderDates, EmployeeShipping.EmpOrderDates FROM Orders, EmployeeShipping WHERE Orders.OrderID = EmployeeShipping.OrderID"

This query is pretty simple and dosn't use foriegn keys, but it should work.

 

[RickSpr]

Does the OrderID field in the EmployeeShipping table refer to the orders in the Orders table? If so, it already is the foreign key. If not, you can still add a foreign key to the Orders table--it isn't necessary that the names match. What matters is that when you create the relationship in the Relationships window, or when you draw a join line in a query grid, you identify the corresponding field in each table.

Kor's reply assumed that EmployeeShipping.OrderID is, in fact, the foreign key to Orders. If that's true, you could use his solution, though actually it's more efficient in Access to use an inner join defined the Access way. In a query grid, that means you drag one of the corresponding fields and drop it on the other, creating a join line. In SQL, it means using the INNER JOIN clause. (Kor's SQL statement is, actually, an inner join, but Access has a special meaning for the term.) Rick Sprague

 

[MelF]

KOR & RICK - Thanks for your responses. No, the OrderID in the EmployeeShippingTable is the primary key for this table, and the OrderID in the Orders table is the primary key for that table. I want to add the OrderID field from the Orders table into the EmployeeShippingTable, but it won't let me because I already have a field called OrderID in the EmployeeShippingTable. So, how do I put one of these tables primary keys into the other table as a foreign key, since both primary key fields are named the same????

Thanks!!!!!!!!!!!!!!!

 

[MelF]

O.K. - After I've thought about this for a while, I'm not sure that it can be done. These tables are both used for orders, one for customers, and one for employees. There are two forms. One based on Orders table, one based on EmployeeShipping table. Within each of these forms is a subform (same subform in both "main" forms) called OrderDetails. This OrderDetails subform is based on the OrderDetails table (inventory table). Both Orders table and EmployeeShippingTable have their own date fields. I want to run a query that will show how many kits (both customer and employee) were ordered within a certain date. I can find a total of ALL products sold, because all inventory transactions from both tables are in the OrderDetails table. And in another report I did get it to query by date, but it takes two parameters being set within the query, one parameter to query each table's date field. Now; however, I'd like to find a way to eliminate the need for two parameters. The only thing I can think of is a field on the OrderDetails form (and in the OrderDetails table) that is based on a query from the Orders and EmployeeOrders tables, which queries both date fields, and autopops into this new field in the OrderDetails form (and OrderDetails table) either the EmployeeDate (if this record is in OrderDetails subform within the EmployeeOrders form) or the OrderDate (if this record is in the OrderDetails subform within the Orders form). I know this is long, but does it make sense!!!???? If it does, are there any suggestions???? Thanks!!!!!!!!!!!

 

[RickSpr]

How about creating separate queries for Orders+Order Details and EmployeeShipping+OrderDetails. Make sure they have the same fields (or at least corresponding fields) in each. Then create a Union query to combine them, and use the date parameter in that one. The only problem is, union queries aren't updatable. Do you need to update?

The only other thing I can think of is to combine your Orders and EmployeeShipping table into a single table, with a column that indicates which type of order it is. But of course, if you do that, you'll have to add selection criteria to everywhere else you've used one of the tables, and add selection criteria and change table names everywhere you've used the other. Doesn't sound like much fun! Rick Sprague

 

[ghubbell]

Why can't your employees be customers too? Unless you're doing this for UPS or United Airlines, it might be less work than all this mucking around. Catagorize your customer by...customer,employee,distributor,mis,programmer/green beer swilling Canadian...then you can even apply different rates based on their catagory...1 ID field to a catagory table, find customers.catagoryID = 1 And 2... Good plan? Gord
ghubbell@total.net

 

[MelF]

RICK & GORD - Thanks for your responses.

RICK - When you say "updateable": I want this query to reflect new records as they're entered into the tables. Is this possible??

Thanks!!!

 

[MelF]

RICK - I went ahead and tried it and it did update!!!!!!!!!!!! Thank you SO MUCH!!!!!!!!!!!!!!! BIG HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!