Queries with spaces in field names (1 Viewer)

[omgjtt]

Here is a question I asked at some point last week.

I am pretty new you access. <= one week : )

I have a database that contains two tables. Each table has two fields.

The data in the left column is the same for each table. It is a naming convention used for a campaign. Lets call that field tbl_Name. The second field in each table is used for tracking a campaign number. Lets call this table tbl_campaignNumber.

Ex:

Table 1

tbl_Name | tbl_campaignNumber

EVBB000 | 19232
EVBB000 | 19233
EVBB000 | 19234
EVBB000 | 19235


Table 2

tbl_Name | tbl_campaignNumber

EVBB000 | 19232
EVBB000 | 19233
EVBB000 | 19234
EVBB000 | 19236
EVBB000 | 19237
EVBB000 | 19238

The second table is a master list. It should have every campaign number. I am hoping someone can help me figure out how to write a query that will return campaign numbers that do not appear in table 2 but are inside table 1.

In the above example it would return - EVBB000 | 19235 since that is in table 1 but not inside table 2. I hope this makes sense : )

I have tried to do an unmatched query using the wizard and the result came back with nothing. Any help would be really appreciated.

Thanks in advance.

I was able to create a query that returned what I needed. Some of the field names have spaces. I understand that this is a bad practice but I am not in control of the naming conventions at this point. To get the result I removed all spaces from the field name.

My question is if it is possible to get the same result without removing spaces in the field name.

Here is the working code

SELECT [loaded sysprins EVR90].comro, [loaded sysprins EVR90].sysprin 
FROM [loaded sysprins EVR90] LEFT JOIN [Inventory SysPrins EVR90] 
ON [loaded sysprins EVR90].sysprin = [Inventory SysPrins EVR90].SysPrin 
WHERE ((([Inventory SysPrins EVR90].SysPrin) Is Null));

Here is the not working code

SELECT [loaded sysprins EVR90].[comro camp_code], [loaded sysprins EVR90].sysprin
FROM [loaded sysprins EVR90] LEFT JOIN [Inventory SysPrins EVR90] 
ON [loaded sysprins EVR90].sysprin = [Inventory SysPrins EVR90].sysprin
WHERE ((([Inventory SysPrins EVR90].sysprin) Is Null));

The only difference I am able to see is the two different field names. The working code has comro and the broken code has [comro camp_code]. The brackets around this broken code cause access to think I am running a parameter query.

I am really lost and would really like a pointer here. Thanks again.

- James

 

[Summer123]

you state the below

The working code has comro and the broken code has [comro camp_code].

but your not working query has

SELECT [loaded sysprins EVR90].[comcast camp_code],

maybe thats the reason why???

 

[spoole]

When referencing field names that have spaces just put the brackets round it:

[Field name has spaces] that was Access know where the field name starts and ends and will not treat spaces as new lines of code to execute etc.

 

[omgjtt]

@ Summer123 - I changed the actual names when posting oh here. I did not want to give company names. I will fix that! Opps. THanks for spotting. In my code the naming conventions are correct.

@ spoole - I did add does. Here is that part of the code - [comro camp_code]
When I run the query with the spaces it returns everything. In the basic example from the first post it would return the following:

EVBB000 | 19232
EVBB000 | 19233
EVBB000 | 19234
EVBB000 | 19236
EVBB000 | 19237
EVBB000 | 19238

When I run the query without spaces it returns the results that are missing from the one table. In the above example it would return EVBB000

Sorry if this is confusing but I am trying to give as much information as I can.

Thanks for your suggetions

- James

 

[Brianwarnock]

If the system asks you to enter the parameter some field name then probably you have missplelt some field name, could be as simple as an extra space.

brian

 

[omgjtt]

Thanks Brianwarnock - I will take a look for that.