[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Concatination in Query Problem.

Posted on 2016-10-05
38
Medium Priority
?
93 Views
Last Modified: 2016-10-15
Please have a look at Query 2 in design mode from sample database. You can see what I am trying to achieve. It does not work, the query is asking for 2 more parameters. I don't know why I need them and cannot see how to get rid of them.

Concatenation Function is in Module4

But the Door Numbers (which is usually but not always Text) in SetsQuery2 is exactly what is required. I need to see all door numbers for each ItemNumber concatenated into a field called results
0
Comment
Question by:DatabaseDek
  • 14
  • 10
  • 8
  • +1
37 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 41830296
What sample database?
0
 

Author Comment

by:DatabaseDek
ID: 41830366
God I'm hopeless!
Concat_2.mdb
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41830436
I would do that differently by using a VBA Function to write the unique door numbers to a temp table while at the same time setting a filter and loop through each door number filtered records building the concatenation string.  Then update your results field in the temp table with the string results.  

Would be a lot cleaner ... IJS.

ET
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 40

Expert Comment

by:PatHartman
ID: 41830470
The result doesn't make sense to me but the query is working correctly.  Please post what you think the resultset should be.  I think it should be two rows
ItemNumber	DoorType	Batch	Location	Result
1732	Type A	1st	Ground	D01, D03
1821	Type B	1st	Ground	B, D

Open in new window

To get to two rows, you must remove DoorNumber since that ends up in the concatenated string.

SELECT Sets.ItemNumber, Item.DoorType, Sets.Batch, Sets.Location, fnConcat("DoorNumber","Sets","ItemNumber = " & [Sets]![ItemNumber] & " And Batch Like " & Chr(34) & Nz([Forms]![ProjectForm]![Batches],"*") & Chr(34) & " And Location Like " & Chr(34) & Nz([Forms]![ProjectForm]![Locations],"*") & Chr(34),", ") AS Result
FROM Item LEFT JOIN Sets ON Item.ItemNumber = Sets.ItemNumber
GROUP BY Sets.ItemNumber, Item.DoorType, Sets.Batch, Sets.Location, fnConcat("DoorNumber","Sets","ItemNumber = " & [Sets]![ItemNumber] & " And Batch Like " & Chr(34) & Nz([Forms]![ProjectForm]![Batches],"*") & Chr(34) & " And Location Like " & Chr(34) & Nz([Forms]![ProjectForm]![Locations],"*") & Chr(34),", ")
HAVING (((Sets.Batch) Like Nz([Forms]![ProjectForm]![Batches],"*") Or (Sets.Batch) Is Null) AND ((Sets.Location) Like Nz([Forms]![ProjectForm]![Locations],"*") Or (Sets.Location) Is Null));
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41830485
since query1 seems to work, why not use it?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41830488
I suspect that this problem has to do with your recordset being based on a query that is based on a query that has two form control references.  I've seen similar behavior before.
0
 

Author Comment

by:DatabaseDek
ID: 41830532
Hi Pat

The sql provided is as Query1. The problem with it is that it does not include door numbers where null values are in either Sets.Batch or Sets.Location So in the example you sent door number A is missing from result in ItemNumber1821.

Result: fnConcat("DoorNumber","Sets","ItemNumber = " & [Sets]![ItemNumber] & " And Batch Like " & Chr(34) & Nz([Forms]![ProjectForm]![Batches],"*") & Chr(34) & " And Location Like " & Chr(34) & Nz([Forms]![ProjectForm]![Locations],"*") & Chr(34),", ")   Does not cater for Null values. This is the sole problem.
0
 

Author Comment

by:DatabaseDek
ID: 41830535
Hi Aikimark

Well I am doing the best I can. It seems more logical to do that rather than a complex statement in the query. I have only had a query based on a query because I do not know why we cannot use the SetsQuery as the data source. I thought that basing a query on a query may eliminate criteria conflictions. There is no other purpose for two queries in my example
0
 

Author Comment

by:DatabaseDek
ID: 41830543
Hi Pat

it is the concatenation that is the problem. The grouping I can sort later. What is actually required here is Concatenate every door number in the current query by ItemNumber and forget batch and Location in the function. I have no idea if that is possible.
0
 

Author Comment

by:DatabaseDek
ID: 41830594
Hi All

I guess this must be a really hard one. Perhaps I need to look in a different direction. I badly need a solution but cannot get anyone to solve the problem or even tell me it is impossible.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41830623
If you created a parameterized version of the query that replaced the form references with parameters, you could use a querydef object to pass these form values into the query programmatically.
0
 

Author Comment

by:DatabaseDek
ID: 41830660
So the user would type the batch or location into the parameters when the query was executed?

If that is correct I take it that there must be a fundamental difference between a form parameter and  a physical parameter entry?

Is it not possible to put "OR Is Null" twice for Batch and Location somewhere in this statement:

Result: fnConcat("DoorNumber","Sets","ItemNumber = " & [Sets]![ItemNumber] & " And Batch Like " & Chr(34) & Nz([Forms]![ProjectForm]![Batches],"*") & Chr(34) & " And Location Like " & Chr(34) & Nz([Forms]![ProjectForm]![Locations],"*") & Chr(34),", ").
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41830661
It is not impossible at all.  It is not even hard, but we are not here to do your work for you.  Our objective is to help you to help yourself and without knowing EXACTLY what the problem is, we are just guessing.  Now that I know what the problem is, I can tell you how to fix it.

The problem is with query1.  Don't build the criteria in the query.  Build it in the function where VBA is a little easier to use than the nested IIF()'s.   The query ends up looking like:
SELECT [DoorNumber] as ConcatField FROM [Sets] WHERE ItemNumber = 1732 And Batch Like "2nd" And Location Like "Floor 2"
What you want to end up with is:
SELECT [DoorNumber] as ConcatField FROM [Sets] WHERE ItemNumber = 1732 And (Batch = '2nd' OR Batch Is Null) And (Location = 'Floor 2' OR Location Is Null)

Also, Never use Like unless you are providing a partial string.  When using a combo, you are always providing a full string so use the = operator.
0
 

Author Comment

by:DatabaseDek
ID: 41830696
Well after struggling with this for 2 days solid Pat I never expected some one to suggest that I want someone to do the work for me. I thought EE was there to solve problems that members could not solve. That's what I see all day long here. It's what I joined for. I have just paid $115 with EE online on this very problem. Hasn't solved the problem at all. Not sure what I am supposed to do. If I can't solve a problem, working on it longer is not going to help. I tried to find you in the list for online help but you were not there (I was typing in Pat Harman) I offered $100 for someone to work on this project I got no responses. I really am struggling so if you could offer something a little more helpful I would be delighted to pay whatever it costs. I have worked with access for 15 years and have built some beautiful stuff but functions are not my strong area, always solved the problem in other ways. I have no formal training. So I am stuck working with what I have. So can you help with this? Paid or otherwise I don't mind.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41830717
I badly need a solution but cannot get anyone to solve the problem or even tell me it is impossible.
I told you what was wrong with Query1.  But, rather than fixing it in the query, I would build the criteria in the function.

I don't take gigs or online calls so that is why you couldn't find me.  Do you understand how to get from what you have for criteria to what you need?  You can change it in query1 if you prefer.  I just wouldn't do it that way.  Part of using a function gets you to simplify the query.  Just pass in the necessary fields and build the criteria string in the function.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41830721
You have code in your form's button click event. In that event, you could pass the values of the form's controls into the parameters of the query.
0
 

Author Comment

by:DatabaseDek
ID: 41830749
Unfortunately Pat I do not have any skills when it comes to functions. I was just looking for a book online to purchase but if I find one that I think I might understand it won't get to me in time to satisfy my customer. You are my only hope unless I can pass your comments onto the Lady that was trying to help me online. But I guess she is in bed by now.

I can tell you exactly what I want but can't help at all with Fuction.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41830755
0
 

Author Comment

by:DatabaseDek
ID: 41830808
Hi etsherman

I am sorry it must have come through just before other posts I didn't see it. I naturally scroll down to bottom so I missed it.

Just a thought though the query that ideally should have this concatenation field has 220 columns. Would it still work. I must admit I prefer the idea of a function handling it but "cleaner" I like. I am not sure if Pat is doing anything with it so rather than having two people working on it let us see if Pat comes up with anything first and if not I would love your help. Derek
0
 

Author Comment

by:DatabaseDek
ID: 41830841
Hi etsherman

Would the temporary table need to be added to the main query linking, presumably, by itemnumber and then just add the concatenated field from the temporary table to the QBE grid?
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41830952
Yes, you could do that.  Then use the OnClose Event of a Form or Report Object to clear the temp table once finished.

ET
0
 

Author Comment

by:DatabaseDek
ID: 41831001
Are you on the online assist or project I have offered $100 for someone to do this
0
 

Author Comment

by:DatabaseDek
ID: 41831005
Oops it's 1.15 am here must go to bed. I will be out all tomorrow. Catch up on Friday

Derek
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41832104
I did not fix your problem.  I determined what it was and told you how to fix it.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41832217
Contact me at the email address in my profile.  I think this is something we need to discuss further especially if you are considering a new approach.

ET
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41832231
etsherman, this is not how EE works.  Discussions are supposed to be public.  If you want to be hired to do the work, use the Gigs or call option.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41832608
Seems like there is even more confusion when I reviewed the comments posted under the Gig for this question.  I'm not familiar with the Call Option.


ET
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41832638
I gave the poster the solution earlier in the thread.  He just doesn't know how to implement it.  So, look back at 41830661 and 41830717.  He needs to fix the part of query1 where he is building the criteria that is used in the function.  I posted an example of what the final string needs to look like.  Apparently someone needs to actually change the concatenation of that string for him to include all the parts.  Don't forget the parentheses since the criteria will now contain both AND and OR operators.
0
 

Author Comment

by:DatabaseDek
ID: 41835182
So Pat

Still can't find anyone on EE Project or otherwise to solve the issue. I don't know how you expected me to solve this when no-one else on the EE network is prepared or able to solve it. Is there anyone you know in EE that is capable of doing this fix either online or as a project. I'm at the point of loosing faith altogether (Bit dramatic but not far from the truth)
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41837178
Do you understand how the string I posted is different from the string you are building?  Did you attempt to modify your concatenation to build the string differently?  It sounded like etsherman was offering to do this for you but didn't.  I don't have time to look at it today but please try it and post back.  I'll look tonight or tomorrow to see how you are doing.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41837221
I did not want to comment on your solution Pat as it would have just created more confusion.  I was willing to provide DatabaseDek with another method.  I hadn't been on EE for a while and they used to have "Hire Me" in your Profile.  That may have changed now.  If he is willing to post it as I gig so I can apply for it ... I would be glad to work with him to get it solved.

What happened to the "Hire Me" option ... is it still there and I am just not seeing it???

ET
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41837419
Eric, please accept the Gig.  I don't do gigs.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41837429
Gustav Brock seems to be working with DatabaseDek on his question.  That is one lengthy discussion.  If he wants to go another route just let him post a new Gig.  It would take me a day just to read up on everything to get caught up to where he is now.  

Thanks,

ET
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41837452
As I said, it really is a simple problem to solve once you understand it and you're right, that Gig Q&A is going nowhere.
0
 

Author Comment

by:DatabaseDek
ID: 41838158
Hi Eric/ Pat

Thanks for your help.

Spent 2 hours on this. Hope it's clear!

I have raised the Project offer to 200 US because I have added another requirement.

Project is called Concatination in Query
https://www.experts-exchange.com/gigs/2543/Concatination-in-Query.html
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41838490
I will opt out of this one as my solution would have involved a VBA function instead of
SQL.  I think DatabaseDek wants to keep it in SQL.

ET
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41838670
Database,
PLEASE try to modify the string as I instructed and post back.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month20 days, 11 hours left to enroll

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question