Select an entry from two liked(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.

 

[WayneRyan]

K,

Paste this in the query's SQL view:

Select A.OrderNumber,
       A.HoursForOrder,
       B.HoursWorked
From   Orders As A Inner Join Reports As B On
         A.OrderNumber = B.OrderNumber
Order By A.OrderNumber

hth,
Wayne

 

[kikeman]

Would this work from C#?

I am ussing OleDB.

Thanks,
Enrique.

 

[MSAccessRookie]

Would this work from C#?

I am ussing OleDB.

Thanks,
Enrique.

From an SQL point of view, the code as written should give you exactly what you need. To use it in C#, you may need to create a Query to call, or perhaps a Query String in C# Format that you can execute. I am not familiar with the exact details.

 

[kikeman]

Would that Query work if the Tables are not linked with a relationship?

Thanks,

 

[WayneRyan]

K,

I also don't know about the C# aspect of it.

But, the query will work WITHOUT explicit relationships set.

Wayne

 

[MSAccessRookie]

Would that Query work if the Tables are not linked with a relationship?

Thanks,

A relationship that could displayed by using the Access Relationships Tool would not need to be be defined, but there would have to be a PK/FK relationship betweeen the tables, or the query would be unable to select the appropriate records.