Access Sql Query help
Hello,
I have a query and a table that I need to join. The table is called Associates and has asKey asFirstName, and asLastName. The query is called JoinAssoc and has Associd which needs to be joined with Associates.asKey. I also need this new query to have SlDate and SalesCount from Query JoinAssoc. It would also be great if I could join the asFirstName and asLastName into one column.
I have a query and a table that I need to join. The table is called Associates and has asKey asFirstName, and asLastName. The query is called JoinAssoc and has Associd which needs to be joined with Associates.asKey. I also need this new query to have SlDate and SalesCount from Query JoinAssoc. It would also be great if I could join the asFirstName and asLastName into one column.
ASKER
Getting type mismatch error.
For Concatination of first name and last name
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName, JoinAssoc.SalesCount , JoinAssoc.SlDate from Associates
Inner Join JoinAssoc ON JoinAssoc.Associd = Associates.asKey
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName, JoinAssoc.SalesCount , JoinAssoc.SlDate from Associates
Inner Join JoinAssoc ON JoinAssoc.Associd = Associates.asKey
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName as Name, JoinAssoc.SalesCount , JoinAssoc.SlDate from Associates
Inner Join JoinAssoc ON JoinAssoc.Associd = Associates.asKey
Inner Join JoinAssoc ON JoinAssoc.Associd = Associates.asKey
JoinAssoc.Associd and Associates.asKey must have of same type
is it ?
is it ?
ASKER
How can i check? there both just an ID number ranging from 1 to 999 that identifies a Sales Associate.
check in table Design
Select table -> Right Click -> Design View
and check what datatype used for both fields ?
and check what datatype used for both fields ?
ASKER
asKey is Text, Associd is a number, can I convert asKey to a number through the query?
Just change it in esign view itself
ASKER
I can't, the database that these tables are in are being used by a Point of Sale software, and changing the data type will cause issues.
Then try this
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName as Name, JoinAssoc.SalesCount , JoinAssoc.SlDate from Associates
Inner Join JoinAssoc ON JoinAssoc.Associd = Cast ( Associates.asKey as int)
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName as Name, JoinAssoc.SalesCount , JoinAssoc.SlDate from Associates
Inner Join JoinAssoc ON JoinAssoc.Associd = Cast ( Associates.asKey as int)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked, thanks.
Inner Join JoinAssoc ON JoinAssoc.Associd = Associates.asKey