We help IT Professionals succeed at work.

Access Sql Query help

Sean Rhudy
Sean Rhudy asked
on
Medium Priority
178 Views
Last Modified: 2012-05-06
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.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Select Associates.*, JoinAssoc.SalesCount , JoinAssoc.SlDate  from Associates
Inner Join JoinAssoc  ON JoinAssoc.Associd  = Associates.asKey
Sean RhudyPresident

Author

Commented:
Getting type mismatch error.
CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT

Commented:
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName as Name, JoinAssoc.SalesCount , JoinAssoc.SlDate  from Associates
Inner Join JoinAssoc  ON JoinAssoc.Associd  = Associates.asKey
CERTIFIED EXPERT

Commented:
JoinAssoc.Associd   and Associates.asKey must have of same type
is it ?
Sean RhudyPresident

Author

Commented:
How can i check? there both just an ID number ranging from 1 to 999 that identifies a Sales Associate.
CERTIFIED EXPERT

Commented:
check in table Design
CERTIFIED EXPERT

Commented:
Select table -> Right Click  -> Design View
and check what datatype used for both fields ?
Sean RhudyPresident

Author

Commented:
asKey is Text, Associd is a number, can I convert asKey to a number through the query?
CERTIFIED EXPERT

Commented:
Just change it in esign view itself
Sean RhudyPresident

Author

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.  
CERTIFIED EXPERT

Commented:
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)
CERTIFIED EXPERT
Commented:
Select Associates.asKey ,Associates.asFirstName & " " & Associates.asLastName as Name, JoinAssoc.SalesCount , JoinAssoc.SlDate  from Associates
Inner Join JoinAssoc  ON JoinAssoc.Associd  = Cint( Associates.asKey )

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Sean RhudyPresident

Author

Commented:
That worked, thanks.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.