-
DataReport Problem
I have 2 table in my access database
Table 1 = tblCustomer
have these fields:
Customer_Number
Visit_ID
Admition
Lap
Minor
Altra
Dressing
Discount
FNA
Table 2 = tblVisits
have these fields:
VisitID
Customer_Number (same as the one in table 1)
Date
Total
Paied
Closed
Doc_Clear
now I want to make DataReport to put all fields in table 1 in text boxes in the report and get Total and Paied from table 2 for each customer number in the report
I mean all data in table 1 + total and paied fields from table 2
i tried this query:
"SELECT b.*, v.* FROM tblBreakDown b INNER JOIN tblVisits ON b.Customer_Number = v.Customer_Number"
is it correct ?
and how to make the report what code should be ?
-
Re: DataReport Problem
-
Re: DataReport Problem
1) What's 'v' (as in v.Customer_Number)? If it's tblVisits, it should be
FROM tblBreakDown b INNER JOIN tblVisits v
2) Don't use b.* and v.* - define the fields you want returned, even if it's all of them. (If there are fields of the same name in both tables [like Customer_Number], they'll be ambiguous in the dataset, so return them something like:
SELECT b.Customer_Number as BCust, v.Customer_Number As VCust ...
Then the report can display BCust in that textbox.)
So your SQL statement should look like
Code:
"Select b.Customer_Number,b.Visit_ID,b.Admition,b.Lap,b.Minor,b.Altra,b.Dressing," & _
b,Discount,b.FNA,v.Total,v.Paied " & _
"FROM tblBreakDown b INNER JOIN tblVisits v ON b.Customer_Number = v.Customer_Number"