Solved

Concatination in Query Problem.

Posted on 2016-10-05
38
52 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
38 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
What sample database?
0
 

Author Comment

by:DatabaseDek
Comment Utility
God I'm hopeless!
Concat_2.mdb
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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 45

Expert Comment

by:aikimark
Comment Utility
since query1 seems to work, why not use it?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
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
Comment Utility
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:DatabaseDek
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Are you on the online assist or project I have offered $100 for someone to do this
0
 

Author Comment

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

Derek
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

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

Expert Comment

by:Eric Sherman
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now