• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

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.
0
Sean Rhudy
Asked:
Sean Rhudy
  • 9
  • 5
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
Sean RhudyPresidentAuthor Commented:
That worked, thanks.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now