Select all entry data from two linked (with a relationship) Tables

[kikeman]

Hi,

I have two tables that are liked with a relationship:

"Orders" and "Reports" with the OrderNumber column.

Orders Table (PK -> OrderNumber):
OrderNumber HoursForOrder
123456 47
876433 63

Reports Table (PK -> ID):
ID OrderNumber HoursWorked
4 123456 5
5 123456 4
6 876433 17

Question:

What would be the SQL SELECT command if I have the "ID" of a Report, and I
would like to get the "HoursForOrder" at the same time with a single command?

Since they are already linked I am wondering if should I really need to
execute two SELECT command (one SELECT to get the "OrderNumber" from
"Reports" and other SELECT to get the "HoursForOrder" from "Orders ") or only
one to get all data because the data is linked with a relationship.

Thanks,
Enrique.

 

[Douglas J. Steele]

Create a query that joins the two.

SELECT Reports.Id, Reports.OrderNumber, Reports.HoursWorked,
Orders.HoursForOrder
FROM Reports INNER JOIN Orders
ON Reports.OrderNumber = Orders.OrderNumber

 

[KARL DEWEY]

Try this --
SELECT ID, OrderNumber, HoursForOrder, Sum([HoursWorked]) AS Total_Worked
FROM Orders LEFT JOIN Reports ON Orders.OrderNumber = Reports.OrderNumber
GROUP BY ID, OrderNumber, HoursForOrder;

 

[Douglas J. Steele]

That won't work, Karl: you'll get a complaint about OrderNumber being
ambiguous, since it exists in both tables and you're not qualifying which
one you want. in the Select clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this --
SELECT ID, OrderNumber, HoursForOrder, Sum([HoursWorked]) AS Total_Worked
FROM Orders LEFT JOIN Reports ON Orders.OrderNumber = Reports.OrderNumber
GROUP BY ID, OrderNumber, HoursForOrder;

--
Build a little, test a little.

Hi,

I have two tables that are liked with a relationship:

"Orders" and "Reports" with the OrderNumber column.

Orders Table (PK -> OrderNumber):
OrderNumber HoursForOrder
123456 47
876433 63

Reports Table (PK -> ID):
ID OrderNumber HoursWorked
4 123456 5
5 123456 4
6 876433 17

Question:

What would be the SQL SELECT command if I have the "ID" of a Report, and
I
would like to get the "HoursForOrder" at the same time with a single
command?

Since they are already linked I am wondering if should I really need to
execute two SELECT command (one SELECT to get the "OrderNumber" from
"Reports" and other SELECT to get the "HoursForOrder" from "Orders ") or
only
one to get all data because the data is linked with a relationship.

Thanks,
Enrique.

 

[ADAO ZETTERMANN]

That won't work, Karl: you'll get a complaint about OrderNumber being
ambiguous, since it exists in both tables and you're not qualifying which
one you want. in the Select clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this --
SELECT ID, OrderNumber, HoursForOrder, Sum([HoursWorked]) AS Total_Worked
FROM Orders LEFT JOIN Reports ON Orders.OrderNumber = Reports.OrderNumber
GROUP BY ID, OrderNumber, HoursForOrder;

--
Build a little, test a little.

Hi,

I have two tables that are liked with a relationship:

"Orders" and "Reports" with the OrderNumber column.

Orders Table (PK -> OrderNumber):
OrderNumber HoursForOrder
123456 47
876433 63

Reports Table (PK -> ID):
ID OrderNumber HoursWorked
4 123456 5
5 123456 4
6 876433 17

Question:

What would be the SQL SELECT command if I have the "ID" of a Report, and
I
would like to get the "HoursForOrder" at the same time with a single
command?

Since they are already linked I am wondering if should I really need to
execute two SELECT command (one SELECT to get the "OrderNumber" from
"Reports" and other SELECT to get the "HoursForOrder" from "Orders ") or
only
one to get all data because the data is linked with a relationship.

Thanks,
Enrique.