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.
Sean RhudyPresidentAsked:
Who is Participating?
 
Pratima PharandeCommented:
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName as Name, JoinAssoc.SalesCount , JoinAssoc.SlDate  from Associates
Inner Join JoinAssoc  ON JoinAssoc.Associd  = Cint( Associates.asKey )
0
 
Pratima PharandeCommented:
Select Associates.*, JoinAssoc.SalesCount , JoinAssoc.SlDate  from Associates
Inner Join JoinAssoc  ON JoinAssoc.Associd  = Associates.asKey
0
 
Sean RhudyPresidentAuthor Commented:
Getting type mismatch error.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Pratima PharandeCommented:
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

0
 
Pratima PharandeCommented:
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName as Name, JoinAssoc.SalesCount , JoinAssoc.SlDate  from Associates
Inner Join JoinAssoc  ON JoinAssoc.Associd  = Associates.asKey
0
 
Pratima PharandeCommented:
JoinAssoc.Associd   and Associates.asKey must have of same type
is it ?
0
 
Sean RhudyPresidentAuthor Commented:
How can i check? there both just an ID number ranging from 1 to 999 that identifies a Sales Associate.
0
 
Pratima PharandeCommented:
check in table Design
0
 
Pratima PharandeCommented:
Select table -> Right Click  -> Design View
and check what datatype used for both fields ?
0
 
Sean RhudyPresidentAuthor Commented:
asKey is Text, Associd is a number, can I convert asKey to a number through the query?
0
 
Pratima PharandeCommented:
Just change it in esign view itself
0
 
Sean RhudyPresidentAuthor Commented:
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.  
0
 
Pratima PharandeCommented:
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)
0
 
Sean RhudyPresidentAuthor Commented:
That worked, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.