From access to sql query

Transact-SQL
1

Hi There,

I am trying to replicate some of my queries of an access database into MS management studio and I came across some issues. I have a query on my access db which returns a Budget Code ( ParentCD ) as well as a Budget Code Description (ParentCDDesc) from a table called BudgetCD which are both short text data type.

When I run the query on access it returns exactly what I want, the ParentCD as numerical format and the ParentCDDesc in text, however when retrieving the same information on management studio using the same table I exported from access it returns the Parent Code (ParentCD) as well as the Parent Code Description (ParentCDDesc) both in numerical format. The two columns are declared as nvarchar(255) on management studio.

Can someone provide some idea to how to make the ParentCDDesc shows as text instead of showing as numerical? Right now the two columns looks exactly the same in management studio. See below.

Access view

ParentCD ParentCDDesc
01 Communication
010 Training
02 Consulting
03 Data Center Services
04 Hardware
05 Operation Expenses
06 Salaries and Compensations
07 Software
08 Supplies
09 System Support Services

SQL Management studio view

ParentCD ParentCDDesc
01 01
010 010
02 02
03 03
04 04
05 05
06 06
07 07
08 08
09 09

2

please show us the schema design of table in sql server and the query you are using to get the data in SSMS

3

And the Access query

4

The Table name is:

BudgetCd

Column names are

BdgCD (nvarchar(255),null)
ParentCD (nvarchar(255),null)
ParentCDDesc(nvarchar(255),null)
CD-Desc(nvarchar(255),null)
Desc_Abb(nvarchar(255),null)
Annual_Budget(int,null)

This is the query in SSMS

Select BCD.[ParentCD], BCD.[ParentCDDesc]
from [dbo].[BudgetCD] as BCD
Group by BCD.[ParentCD], BCD.[ParentCDDesc];

This is the Access query

SELECT BudgetCD.ParentCD, BudgetCD.ParentCDDesc
FROM BudgetCD
GROUP BY BudgetCD.ParentCD, BudgetCD.ParentCDDesc;

5

and you are seeing this here below in the UI of the app or in SSMS itself?

6

This is the query results in MSAccess

ParentCD ParentCDDesc
01 Communication
010 Training
02 Consulting
03 Data Center Services
04 Hardware
05 Operation Expenses
06 Salaries and Compensations
07 Software
08 Supplies
09 System Support Services

This is the results in SSMS

ParentCD ParentCDDesc
01 01
010 010
02 02
03 03
04 04
05 05
06 06
07 07
08 08
09 09

I need the results in SSMS to show just like MSAccess

7

What do you get if you run

Select top(10) BCD.[ParentCD], BCD.[ParentCDDesc]
from [dbo].[BudgetCD] as BCD;
8

This is the result

ParentCD ParentCDDesc
01 01
01 01
01 01
010 010
010 010
02 02
02 02
02 02
03 03
03 03

9

This tells me Access is using a different table.

10

the tables I am using in SSMS were exported right from the access db

11

It looks like the export was of a different table/view than what Access is using in the query.

The SQL table does not have the data show in your Access table so that means there is a disconnect somewhere.

12

what results do you get when doing following query

Select BCD.[ParentCDDesc]
from [dbo].[BudgetCD] as BCD
13

ParentCDDesc
01
01
01
010
010
02
02
02
03
03
03
03
03
03
04
04
04
04
04
04
04
04
05
05
05
06
06
07
07
07
07
07
07
07
08
09
09
09
09
09
09
09

14

so there you go. your data migration from Access to SQL did not work.both ParentCD and ParentCDDesc have the same data in them

15

I think the problem s on the data type using in the two tables, in access the column is using data type shorttext in while in SSMSl is using nvarchar(255) however the same table has two other columns called
[BdgCD], [Decription_Abb] respectively this is the query

Select [BdgCD], [Decription_Abb]
from [dbo].[BudgetCD] as BCD

and this is the result

BdgCD Decription_Abb
01.001 Cervalis Comm
01.002 MTVL Comm
01.003 NY Comm
010.001 IT Trng
010.002 IT Trng TR Exp
02.001 Sys Prof Serv
02.002 Bus Soft Arc Dev
02.003 Sys Soft Arc Dev
03.001 Cerv DC Ann Rent
03.002 Cerv DC SetUp
03.003 MTVL DC Ann Rent
03.004 MTVL DC Gen Maint
03.005 Supp Comp
03.006 Tech Serv
04.001 Cisco R,S,F,K
04.002 EMC 2500 DD
04.003 VMWARE Srv Exp
04.004 Desktp Equip
04.005 EMC 2500
04.006 Cisco Srv Rack Mount
04.007 VMWARE Srv Exp
04.008 Cisco R,S,F,K
05.001 IT Trvl Exp DC
05.002 IT Trvl Exp CA
05.003 IT Oper Exp
06.001 Mthly Comp
06.002 Spec Bonus
07.001 VEEAM Bup Sol Sware
07.002 EMC DD 2500 Sware
07.003 Core Swt Nex 7K
07.004 Cisco UCS Lic
07.005 Bus Sware APP
07.006 Desktp Comp Lic
07.007 Other Lic
08.001 Desktp Supp Acc
09.001 IBM Maint
09.002 Cisco Snet
09.003 VEEAM Bup Pjt Supp
09.004 Netwk Sec Dom Rnew
09.005 MS Off 365
09.006 Sec Rel Rnew
09.007 Ven Spp Serv

This is exactly how I need the other two columns to print, they are all using the same data type in access as well as in SSMS I don't know why the other two columns are not printing like this

16

Yes that was my thought I tried changing the data type but still getting the same results. Can you suggest me the correct data type in ssms sorry I am a newvy in ssms

17

in ssms

  1. go the database where BudgetCD exists
  2. Expand database
  3. go to the Tables and expand that
  4. Find BudgetCD then right click on BudgetCD and select Script Table as then CREATE to New Query Editor

post the script back here

18

Thanks @yosiasz for helping @susendy , I have been away from Access for a long time so I do not remember how to do these things.

19

yosiasz
I did exactly what you told me to do in your last reply but I am still having the same result

20

susendy. Please follow this request and post script back

in ssms

  1. go the database where BudgetCD exists
  2. Expand database
  3. go to the Tables and expand that
  4. Find BudgetCD then right click on BudgetCD and select Script Table as then CREATE to New Query Editor