Issue with Query (1 Viewer)

[tmyers]

I have a query I put together with a little help from a friend that doesn't seem to want to work. I keep getting pop ups wanting parameters.

Select a.[customer name], b.[customer name] as EdgeName, a.[account #], b.[jobid],  NZ(a.[inside sales], b.[inside sales]) as insidesales, NZ(a.[outside sales],  b.[outside sales]) as outsidesales
from tblCustomers as a
left join tblCustomers as b on a.[account] = b.[lookupaccount]
order by a.[customer name], b.[jobid]

My friend has a much better understanding of SQL then I do myself and he said he was able to get it to work on his end, but mine just throws about 6 parameter windows. Any idea why that is? I am running on Access 2016 and I think he has 2019 (or whatever version is newer) which might explain why it works for one of us.

 

[theDBguy]

Only thing I could think of is you both have different table structure, perhaps different field names? What parameters were you getting?

 

[tmyers]

I was getting parameters on:
a.[customer name], b.[customer name], a.[account #], b.[jobid], NZ(a.[inside sales], b.[inside sales])

Essentially the entire Select lol.
The table is tblCustomers, as shown in the SQL statement.
Here are the fields:

 

[The_Doc_Man]

I won't say "always" but the most common cause for an unexpected parameter popup is that SQL cannot find the thing you name. This can occur because (a) bad spelling of the field name; (b) the combination x.y is the wrong place to look for y because it isn't in x; (c) the field in question isn't visible at the moment.

In your case, since that is a self-join, my cases (b) and (c) are kind of not possible. But this much is fairly reliable... when Access asks you for a parameter, the name it uses is exactly the spelling you gave it and is exactly the spelling of the data it wants. Therefore, whatever it can't find is exactly what it TOLD you it can't find.

I'm going to make a couple of observations. You have a field designated as [account #] - and having a special character is ALWAYS a bad choice for a field name. There is nothing wrong with saying [accountno]

You also have an NZ that names two fields. I'm going to suggest that the better construct might be

IIF( ISNULL( a.[inside sales] ), b.[inside sales], a.[inside sales] ) as [insidesales]

The question I would have HERE is, what do you do if a.[inside sales] and b.[inside sales] are BOTH null?

Then the same comment applies for the [outside sales] fields.

Finally, if you were to recast the field names to not include spaces, you would do less typing AND not get into trouble if you forget a bracket, because with no special characters and no embedded spaces in names, you don't NEED the brackets.

 

[theDBguy]

I was getting parameters on:
a.[customer name], b.[customer name], a.[account #], b.[jobid], NZ(a.[inside sales], b.[inside sales])

Essentially the entire Select lol.
The table is tblCustomers, as shown in the SQL statement.
Here are the fields:
View attachment 101270

It looks like a simple query. You should be able to recreate that using the Query Grid Designer. Give it a try and then compare the two SQL statements to see if you spot any differences.

 

[tmyers]

I fully agree with the field naming convention Doc. The reason for the naming is that is how our sales database stores it and I did not change it for simplicity on the importing of the Excel file. As for the inside/outside sales, outside sales has a decent possibility of being null as not every account has an outside salesman, however every account WILL have an inside sales number as if none is provided, the system automatically assigns it the house number. Per our IT, a blank inside sales number is not possible.

 

[tmyers]

It was names that were the problem and I have no idea how I did not notice it.
Reconstructed the query per DBGuy's suggestion and it worked after being rewritten. I also fully identified the tables this time.

SELECT tblCustomers.[Customer Name], tblCustomers_1.[Customer Name] as EdgeName, tblCustomers.[Account #], tblCustomers_1.[Inside Sales #] as InsideSales, NZ(tblCustomers.[Outside Sales #],  tblCustomers_1.[Outside Sales #]) as OutsideSales
FROM tblCustomers AS tblCustomers_1 LEFT JOIN tblCustomers ON tblCustomers_1.LookupAccount = tblCustomers.[Account #]
ORDER BY tblCustomers.[Customer Name]

 

[theDBguy]

It was names that were the problem and I have no idea how I did not notice it.
Reconstructed the query per DBGuy's suggestion and it worked after being rewritten. I also fully identified the tables this time.

SELECT tblCustomers.[Customer Name], tblCustomers_1.[Customer Name] as EdgeName, tblCustomers.[Account #], tblCustomers_1.[Inside Sales #] as InsideSales, NZ(tblCustomers.[Outside Sales #],  tblCustomers_1.[Outside Sales #]) as OutsideSales
FROM tblCustomers AS tblCustomers_1 LEFT JOIN tblCustomers ON tblCustomers_1.LookupAccount = tblCustomers.[Account #]
ORDER BY tblCustomers.[Customer Name]

Glad to hear you got it sorted out. Good luck with the rest of your project.

 

[Pat Hartman]

I did not change it for simplicity on the importing of the Excel file

Faulty logic. You import ONCE but you use forever.