SQL question that is tough concerning multiple fields in a table

[Guest_imported]

heres the situation.
My tables are named tbltransaction and tblcustomer
One to many relationship between CutomerId(one in the tblcustomer)to many(tbltransaction-CustomerId)
Using access 2000 database
Fields in this table are named CustomerId a text field
and TransactionId which is an auto number-Long
my variables are lngtid as long-Used for number the user inputs for the TransactionId based on this
I need the correct way to use sql in Vb to get the result of The TransactionId matching the user input and the One to many CustomerId..Example CusotmerId 1 had transaction 2 for instance and I need all info from both tables that match..
Here is my attemp but I cant get it to work..
ssql="Select * From tblcustomer,tbltransaction Where [TransactionId]=" & lngtid & '" and tblcustomer.[CustomerId]=tbltansaction.[CustomerId]"
I get that error about tbltransaction is a variable that is not defined!Please help.....

 

[MrEGuest]

Is it tblTansaction or tblTransaction?

I'll assume that your table is actually named tblTransaction, but in the excerpt above you refer to an entity name tblTansaction. If this statement was copied directly from the editor, the typo might be your problem.

Good Luck,
Josh

 

[Guest_imported]

It is tbltransaction I guess I was just lazy...But it still does not work..So now what to do with this.Im lost

 

[DJTN]

Do you have SQL managment? You can use it build the strings for what you want. It is really easy. Just from looking at your code it appears that you need a join or inner join in the from statement.
Todd Norris
Hope this helps !

 

[evoluder]

I am not sure exactly what you are after but give the following a shot:

ssql = "SELECT * FROM tblcustomer, tbltransaction WHERE (tbltransaction.TransactionId = " & lngtid & &quot AND (tblcustomer.CustomerId = tbltransaction.CustomerId)"

This would need to be all on one line or you can split it up and join it as you see fit. Change the caps on the fieldnames as appropriate. This statement should return all fields from tblcustomer and tbltransaction for one customer and one transaction. If you are not getting the data back that you want, you may need to do a join or get rid of the * and use another argument. Let me know.

Good luck.

 

[Imhoteb]

hi,

your problem is that the 2 fields are not from the same type. One field is a number(autonumber) and the other field is text. If you want to compare the fields, they must be of the same type. Try to change the text field in your database to a numberfield.

this is what you write
Fields in this table are named CustomerId a text field
Is customerID in both tabels from the same Type?
Did I mistunderstood this or are the two fields textfields?
If this is the case the you have to put single quotes in your sql statement.

ssql="Select * From tblcustomer,tbltransaction Where [TransactionId]='" & lngtid & "' and tblcustomer.[CustomerId]=tbltansaction.[CustomerId]"

 

[BoghieWanKanobe]

Howdy,

You will need either a LEFT JOIN or an INNER JOIN.

cSQL = "SELECT tblCustomer.*, tblTransaction FROM tblCustomer LEFT JOIN tblTransaction ON CLng(tblCustomer.CustomerId) = tblTransaction.TransactionId"

The above posters are right in that:
1. JOINING by a field may require that both fields be of the same type.
2. You may have to create field name aliases for fields of the same name in different tables.

Hint:
Use Access to create a Query. Then switch the view to SQL. The default is WorkSheet view which displays the results of the query.

 

[Funinc]

Try This....
ssql="Select * From tbltransaction inner join tblcustomer on tbltransaction.[CustomerId]=tblcustomer.[CustomerId] Where [TransactionId]=" & lngtid