Select columns from two different table with different fields in one query Access database

2
votes

I have the table tbl_Discount with these columns:

  • d_ID
  • Discount

And the table tbl_Tarrif with these columns:

  • p_ID
  • Price

I want to select Discount and Price in a single query and it's easy in SQL Server using a stored procedure but, in Access I don't know how to do it.

I tried this:

SELECT Discount
  FROM tbl_Discount
 WHERE (d_ID = ?)
 UNION
SELECT Price
  FROM tbl_Tariff
 WHERE (p_ID = ?)

But it returns two rows with one column each:

Discount
0
75000

And I want two columns (discount, price) on a single row, like this:

Discount Price
0        75000
1
sqlms-accessselect
add Access tag in answer, please – Andrei Konstantinov
is d_ID a foreign key from tbl_Tariff in tbl_Discount ? – Taher Khorshidi
@Andrew Spartan I tried to add it but it doesn't allow me to add it. – user3250818
@Homayoun no this two table haden't any realation with each other – user3250818
You say it is easy to do in SQL Server as a sproc. Why not post that stored procedure here so we are sure what you are trying to translate to Access? – Karl Kieninger

1 Answers

1
votes

try this :

SELECT Discount , Price
FROM tbl_Discount, tbl_Tariff
WHERE d_ID = ? AND p_ID = ?