Solved

Concatination in Sample DB

Posted on 2016-10-01
14
57 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
  • 8
  • 6
14 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:als315
Comment Utility
Try this sample
Concat_2.mdb
0
 

Author Comment

by:DatabaseDek
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Expert Comment

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

Author Comment

by:DatabaseDek
Comment Utility
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
Comment Utility
Help!
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry I was absolutely sure I had posted the sample database. I have attached it above
0
 
LVL 39

Accepted Solution

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

728 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

9 Experts available now in Live!

Get 1:1 Help Now