Query and Subquery help request

[IvenBach]

I am trying to move the information I'm using as a database from Excel into Access. I've tried to normalize (I think that's the correct term) my data and create separate tables. What I would like to do is concatenate all the fields (columns) into 1 using a subquery but I have no experience with access and am really struggling. I managed to make a query that creates the basic table and populates it with the correct information.

Below is a UDF in excel that will do what I want but don't know where to begin Access. This is essentially building a list item group to list product features on a website.

Function FeaturesBuild(rngRange As Range)
Dim r As Range
    For Each r In rngRange
        If r.Value <> "" Then FeaturesBuild = FeaturesBuild & "LI" & r.Value & "/LI" & Chr(10)
    Next
End Function

'LI should be html format but doesn't display properly.

I have tried to use the IIF function in Access but it would take the total records on my database and square it (for me was 650^2=422,500) or by playing with it would give me a multiple of the type of data I was working with.

<table align="center" border="1"> <tbody><tr> <td>ID</td> <td>Product Code</td> <td>Body Information</td> <td>Bezel Color</td> <td>Face Color</td> <td>Additional Fields....</td> <td>Concatenated Field Desired</td> </tr> <tr> <td>1</td> <td>Example 1</td> <td>Product Information</td> <td>Black</td> <td>Black</td> <td>More Info</td> <td>Black - Black - More Info</td> </tr> </tbody></table>

 

[Norie]

Why do you want to concatenate the fields?

Is it just for output as HTML?

How do you want to concatenate them and where does Iif come into it?

I don't quite see how a subquery would be involved either.

You could probably create a UDF for this just as you would in Excel but using Access syntax/objects etc.

How did you try to normalize the data?

 

[IvenBach]

1: My desire to concatenate comes from the way that product features are listed. They are grouped as a clump of items similar to my example below. By concatenating the desired fields I can choose which will be included and/or excluded.
2: The output will be for html. The company for online sales that provides the storefront only allows csv files for updating of product information.
3/4: I would like to concatenate them using "[li]TEXT[li] & chr(10)" format for each field that would be included. My Iif was coming into consideration as FeaturesList: IIf([qTable]![DesiredField:]=Null,"","[li]" & [qTable]![DesiredField:]) & "[/li]" & chr(10). I was using a criteria within the query results to hopefully limit and build the concatenated field (this considered a subquery?). Basically if the field is empty, doesn't apply to that product, it will not add it to the field. As it is right now it has [li][/li] come up as the results.
5: If I use a custom function or build it with queries it'd be fine for me either way. Would actually like to have both explained and/or shown to me that way I can better understand Access and have more options open to me in the future.
6: I tried to normalize my data by having one field BezelColor call my table that holds the color information. There are only 5 colors currently (Chrome,Gold,Black,Silver,Anodized Blue) and their primary key is an Autonumber field. Within the design view of my working query that returns the proper text for the field I use the Join Properties with my Left Table be the main list of all my product that holds numbers 1-5 for the BezelColor in this example. My right table is BezelColor and I have option 2 being used that has a right arrow from my main table to my referenced table. This has given me the correct results that I want. Bear in mind that I've no experience with Access and am hoping I got my terms right regarding normalizing data.

[li]Feature 1:[/li]
[li]Feature 2:[/li]
[li]Feature 3:[/li]
...

If I'm still not fully explaining I'll do what I can to reword or clarify. Please bear with me as i venture into Access .

 

[Norie]

Right, if you want to stick with an expression (formula) then use Nz instead of loads of Iifs.

Even if you used Iifs it would probably be Is Null instead of = Null.

Nz works like this:

Nz([Field], value if field is null)

So if you had this:

Nz([Field1], "Who do you think you are, Field 1 doesn't have a value")

And [Field1] was Null it would returt 'Who....' etc.

Obviously you'll want to use something shorter of course eg "".

Even if you use Nz it's probably not going to do what you want.

That's why I suggested a UDF, though I don't really think UDF is used much in Access, more of an Excel thing.

Anyway, what I was thinking was a UDF that takes a Parameter array and converts that into the list.

You would pass all the fields that could be in the list to the function.

It would then go through them and if a field isn't null add it to the list.

By the way, I do mean an HTML 'list'.

Is this making sense at all?

 

[IvenBach]

That does seem like what I am aiming for. In principle it's simple, but I've no idea where to begin. My first thought that comes to mind is I'd kind of want it to be the opposite of the example you gave me. If(EXPR IS NULL,"" [or Null], "[li]" & EXPR & "[/li]" & Chr(10)). If nothing is in the field that's being checked, IE its something that doesn't apply to that product skip it, otherwise wrap it in List Code tags.

I don't mean to sound presumptuous but can you build the syntax or give me a bit of framework so I can make some headway on this? I have tried to understand some of the Access syntax on how to do it but am grasping at straws. I'm all for giving people the tools they need so they can do it themselves. If I'm a bit dense with these newer tools I'm truly trying and want to do things right; a bit of 'hand-holding' as it were may be needed to start me off.

Regarding my explanation of normalizing my data was I correct in my assumption and setup? Just looking for confirmation so my vocabulary is what it should be for future posts.

 

[Norie]

I think you misunderstand Nz, or perhaps I'm misunderstanding what you have.

Are you trying to skip null/empty values or empty strings?

In Access they are different things.

If a field doesn't have anything in it it's Null, if it has something in it, even an empty string, it's not Null.

As for the syntax, it's not far off what you posted for Excel.

Rather than a range you would pass the parameter array and loop through that.

Here's a simple example.

Function CreateList(ParamArray varFieldList()) As String
Dim I As Long
Dim strList As String
    For I = LBound(varFieldList) To UBound(varFieldList)
           If Not IsNull(varFieldList(I)) Then
                strList = strList & "<li>" & varFieldList(I) & "</li>"
           End If
    Next I
    CreateList = strList
 
End Function

This is how you would use it in a query.

SELECT tblHTMLList.ID, tblHTMLList.[Product Code], tblHTMLList.[Body Information], tblHTMLList.[Bezel Color], tblHTMLList.[Face Color], tblHTMLList.[Additional Field], CreateList([Bezel Color],[Face Color],[Additional Field]) AS HTMLList
FROM tblHTMLList;

 

[IvenBach]

The code you provided is basically what I've been trying to accomplish.

I think the problem that I've been having has to do with the fact that I continually have an 'Enter Parameter Value' Box come up every time I'm trying to use the fields I want. I previously came up with a query field of Bezel_Color: IIf([qBase Table]![Bezel Color]=Null,"","<li>Bezel Color: " & [qBase Table]![Bezel Color]) & "</li>" that doesn't ask for a parameter. This was as close as I've come to solving my problem but it would include the blanks. I am quite frustrated and confused why yours is asking me for a paramater for each field I'm including and have it return that same value for all records, while mine doesn't.

Is there some bit of information that I'm not connecting? Sorry for any difficulties with my lead brain.

 

[Norie]

I created a table called tblHTMLList from the data you posted and then used a query with the SQL I posted without problem.

How did you try the function?

 

[IvenBach]

I tried the function that you gave me by adding it in to my query and it came up with the Input Parameter Every time. I started at base 0 and made a completely new trial database with 1 record and attempted to use your function to compile the list and Success! . After that worked I played around with it for a while trying to see what would cause the Parameter to be requested. It seems like it occurs when I'm pointing to any other table even if that new table ([A1-Base Table] OR [qBase Table]) has that field name. When I refer it to table and field [Bezel Color].[Bezel Color] it returns the correct value I've been wanting and builds it properly in the list format via your UDF. When I try to have it refer to anything else it seems like that's when it's wanting the Parameter it's missing.

Should there be any problem with it referring to the same query that its based in? IE I want it to be its own field within the table [qBase Table] where it would use the fields I'm working.

This SQL code works

CreateList([Bezel Color].[Bezel Color],[Dimension].[Dimension]) AS Features

Whereas this doesn't.

CreateList([qBase Table].[Bezel Color],[qBase Table].[Dimension]) AS Features

I was wondering if this works so I could format every field the way I want it to work and then have it simply join them in the Features field. Making any edits easier for me by giving me a field to adjust and not worry about screwing up the entire join field. BTW Thank you for helping me through this Norie. I'm starting to feel a bit better but have a long way to go.

 

[Norie]

The code I posted doesn't need a table reference for the fields.

Does it work without them?

All the code does is take values, those values could be hardcoded if you wanted.

I can only think there's something else going on, perhaps connected with using the same name for table and field.

That just isn't a good idea.

Perhaps if you posted the SQL for the query?

You can just goto View>SQL to see it.