Solved

Concatination in Sample DB

Posted on 2016-10-01
14
63 Views
Last Modified: 2016-10-04
Hi All

I have a door manufacturing customer that would you believe makes domestic doors. It is common in the industry to have a door described as door type  and then all of the individual doors have there own door number. So a typical order may be 20 Type A and then door numbers D01 to D20. But often doors are not manufactured by type, rather by batch. So there could be 8 in the 1st batch and 12 in the second. So I need a way for the 8 doors in the first batch to read Door type "Type A" where door numbers in 1st batch are "D01, D06, D07, D12 etc.

If you look at query 1 then query 2 and then 3 it should show what is required. Basically it is concatenating all of the door numbers associated with different batches (1st and 2nd). I have included the concat code (Module4) used in a form that concatenates all of the doors associated with a door type listed on a form. But I really need the results   in a query.

The last op is easy I can use grouping to compress the data as in "Final result"
Concat.mdb
0
Comment
Question by:DatabaseDek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 40

Expert Comment

by:als315
ID: 41824982
Try this query:
SELECT Count(Item.ItemNumber) AS CountOfItemNumber, Item.DoorType, Sets.Batch, fnConcat("DoorNumber","Sets","ItemNumber = " & [Item]![ItemNumber] & " And Batch = " & Chr(34) & [Batch] & Chr(34),",") AS Result
FROM Item LEFT JOIN Sets ON Item.ItemNumber = Sets.ItemNumber
GROUP BY Item.DoorType, Sets.Batch, fnConcat("DoorNumber","Sets","ItemNumber = " & [Item]![ItemNumber] & " And Batch = " & Chr(34) & [Batch] & Chr(34),",");

Open in new window

0
 

Author Comment

by:DatabaseDek
ID: 41825196
I can't get that to work for me. Did you try it in the database sample?. I get DAO types not defined
0
 

Author Comment

by:DatabaseDek
ID: 41825290
Thank you.

Strange, it works in the full database but not in the sample. But here is the problem. Trying to make the sample easy to understand I guess here is the wrong thing to do. Please have another look. The sample database opens with a form. Select from the first or second combo one of two choices and you will see what I am trying to achieve when you click open query. The user needs to filter the records by whichever batch or location is required. And then see the door numbers only for the selection in the result field. If you can create sql to replace the result field with a concat field that would solve all of my problems.

I have tried replacing the "Sets" table name with a query that would mirror the selection query but It tells me I have parameters missing. Presumably because I no l0nger need the Batch criteria in the sql?
Concat.mdb
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:als315
ID: 41825311
Check references. You need "Microsof Office XX.X Access database engine Objects library"
Compile Module4 at first to be sure all libraries are presented.
What Access version is used?
Concat_1.mdb
0
 

Author Comment

by:DatabaseDek
ID: 41825582
Thank you for doing that! I am so relieved to be getting somewhere with this.

There is a further problem. When no batches or Locations are selected we need to see all door numbers concatenated into one field for each door type. Regardless of Batch or Location. So I am not sure if the reference to Batch in the code is needed. If I could just get it to concatenate only the door numbers in Query1 that would be great. This is why I thought if necessary I could get the numbers from another query instead of "Sets" Table. But I just cannot get that to work.

So if you just click the query button without making any Batch or Location selection, for Quantity 3 of Type A we should get Result: D01, D02, D03 and for Quantity 5 of Type B we should get A, B, C, D, E. I don't even know if this is possible but I need to achieve it somehow.

I am using 2002. Yes I know, but it does everything that I want.

Thank you again
Derek
0
 
LVL 40

Expert Comment

by:als315
ID: 41825618
Try this sample
Concat_2.mdb
0
 

Author Comment

by:DatabaseDek
ID: 41825705
Well that is brilliant to the point where I can almost understand it!.

When I put it into my main database it works perfect except where the user has not provided a Location (and sometimes no Batch). So for small orders they don't want to enter a Batch or Location. Can this be done or is it impossible?

Also I use
Query field "Location" and criteria Like nz([Forms]![ProjectForm]![Locations],"*") Or Is Null,

where you use
Query field  "L: IIf(IsNull([Forms]![ProjectForm]![Locations]),"",[Location])"  and criteria Like "" & [Forms]![ProjectForm]![Locations]         is there any difference?

Again my thanks
Derek
0
 
LVL 40

Expert Comment

by:als315
ID: 41825854
I hope there should be no difference. Can you adapt sample to real DB?
0
 

Author Comment

by:DatabaseDek
ID: 41825956
Thank you for your patients with this. It all works except the missing door numbers. I am not sure here if you are asking me to send full database. It's too complex has 10,000 lines of code. and an enormous amount of dependencies. It took me two hours to make the sample but it does represent closely the problem.

As I said earlier what I do not understand is why is it that we can get the data from the "Sets" table but not a query. I thought Access just treated a query as a table? I can isolate all the data and hopefully we can just say concatenate everything from "SetsQuery" where ItemNumber = ItemNumber

You can see the problem clearly now. If you look at Sets Table I have deleted a value from Batch and one from Location. So you can see that the corresponding Door numbers no longer appear in Query1.

I have added a "SetsQuery" which consistently gives the correct data. And a Quer3 where I am trying to use it. Doesn't work but hopefully it will show the idea. In Query3 I do not know if "SetsQuery" is even needed.
0
 

Author Comment

by:DatabaseDek
ID: 41827033
Help!
0
 
LVL 40

Expert Comment

by:als315
ID: 41827056
You can always change type of query to "create table", run it and use saved table in sample. There is no difference between table and query, but some fields in query may have other type then in table. Type of field is very important for criteria. For debugging of concat function set breakpoint to string with SQL text  and look at it. You can also paste this text to new query and it should work.
0
 

Author Comment

by:DatabaseDek
ID: 41827089
Thank you.

For some reason I cannot get it to work I spent hours on it yesterday.

Are you busy? Is there any chance you could look at the sample database. Looking at what you have done so far it is clearly something that you are familiar with. But I just cannot get it. The query I suggest is SetsQuery and I have tried to use it in Query3. I feel sure that something silly is missing from my effort but I cannot see what it is.

I am up against it here. Just a bit.
Derek
Concat_2.mdb
0
 

Author Comment

by:DatabaseDek
ID: 41827166
Sorry I was absolutely sure I had posted the sample database. I have attached it above
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 41827467
You can't use query with parameters this way. Move criteria to function as was shown in my sample
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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