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>

 

[IvenBach]

My basic product information table is [qBase Table]. The code as follows:

SELECT [tblA1-Base Table].[Product Code], IIf([tblFace Color]![Face Color] Is Null,Null,"Face: " & [tblFace Color]![Face Color]) AS [Face Color], IIf([tblBezel Color]![Bezel Color] Is Null,Null,"Bezel: " & [tblBezel Color]![Bezel Color]) AS [Bezel Color], IIf([tblPointer Color]![Pointer Color] Is Null,Null,"Pointer: " & [tblPointer Color]![Pointer Color]) AS [Pointer Color], IIf([tblDimension]!Dimension Is Null,Null,"Gauge Size: " & [tblDimension]!Dimension) AS Dimension, IIf([tblOperation]![Operation] Is Null,Null,"Operation: " & [tblOperation]![Operation]) AS Operation, IIf([tblRange]![Range] Is Null Or [tblScale]![Scale (Units)] Is Null,Null,"Calibration: " & [tblRange]![Range] & " " & [tblScale]![Scale (Units)]) AS Range
FROM (((((([tblA1-Base Table] LEFT JOIN [tblBezel Color] ON [tblA1-Base Table].[Bezel Color] = [tblBezel Color].ID) LEFT JOIN tblDimension ON [tblA1-Base Table].Dimension = tblDimension.ID) LEFT JOIN [tblFace Color] ON [tblA1-Base Table].[Face Color] = [tblFace Color].ID) LEFT JOIN tblOperation ON [tblA1-Base Table].Operation = tblOperation.ID) LEFT JOIN tblRange ON [tblA1-Base Table].Range = tblRange.ID) LEFT JOIN [tblPointer Color] ON [tblA1-Base Table].[Pointer Color] = [tblPointer Color].ID) LEFT JOIN tblScale ON [tblA1-Base Table].Scale = tblScale.ID;

For my query that works in qProductFeatures:

SELECT [tblA1-Base Table].[Product Code], CreateList([qBase Table]![Face Color],[qBase Table]![Bezel Color],[qBase Table]![Pointer Color],[qBase Table]![Dimension],[qBase Table]![Operation],[qBase Table]![Range]) AS Expr1
FROM [tblA1-Base Table] LEFT JOIN [qBase Table] ON [tblA1-Base Table].[Product Code] = [qBase Table].[Product Code];

I would like to have the option of including the results of my query inside of my base table in some cases but that's when I get the Parameter Input request. Thanks for helping me through this...

I have seen both

.[FIELD] and

![FIELD] used in the query. Is there a time when one should be used and not the other?

 

[Norie]

I don't think I've ever seen 7 joins in one query.

What's in tables like [tblBezel Color]?

Can you post some 'real' sample data?

 

[IvenBach]

<table border="0"><tbody><tr><th scope="col"><table border="01"><tbody><tr><th colspan="2">tblBezel Color
</th></tr> <tr> <th scope="col">ID</th> <th scope="col">Bezel Color</th> </tr> <tr> <td>1</td> <td>Chrome</td> </tr> <tr> <td>2</td> <td>Gold</td> </tr> <tr> <td>3</td> <td>Black</td> </tr> <tr> <td>4</td> <td>Silver</td> </tr> <tr> <td>5</td> <td>Anodized Blue </td> </tr> </tbody> </table></th> <th scope="col"><table border="1"><tbody><tr><th colspan="2">tblDimension</th> </tr> <tr> <th>ID

</th><th>Dimension

</th></tr> <tr> <td>1</td> <td>2-1/16" (52.4mm)</td> </tr> <tr> <td>2</td> <td>1-1/2" (38.1mm)</td> </tr> <tr> <td>3</td> <td>2-5/8" (66.7mm)</td> </tr> <tr> <td>4</td> <td>3-3/4" (95.3mm)</td> </tr> </tbody></table></th> <th scope="col"><table border="01"><tbody><tr><th colspan="2">tblFace Color
</th></tr> <tr> <th scope="col">ID</th> <th scope="col">Face Color</th> </tr> <tr> <td>1</td> <td>Beige</td> </tr> <tr> <td>2</td> <td>Silver</td> </tr> <tr> <td>3</td> <td>Black</td> </tr> <tr> <td>4</td> <td>White</td> </tr> <tr> <td>5</td> <td>Luminescent</td> </tr> <tr> <td>6</td> <td>Carbon Fiber</td> </tr> <tr> <td>7</td> <td>Chrome</td> </tr> </tbody> </table></th> </tr> </tbody></table>
And that's basically what each of my small tables consist of (Dimension, Face Color, Operation, Range, Scale). They all have only a couple of rows. Sometimes I can get info from manufacturers as a spreadsheet while others I have to build from pulling info from their website for each product. At times I have to do a lot of customizing and building pieces of info together to form a new field that will be then used in several other fields. Does that make it a bit more clear?

 

[Norie]

I'm not sure you should have all these tables.

For example, the Bezel Color and Face Color share some values, so why not just have a Color table.

As for dimension, that should just be a field in the main table.

I think some of the other things you mention might not warrant a separate table either.

What might make things a lot clearer would be some sample data including some full records.

 

[IvenBach]

Since it seems I should condense some of the tables into my main is there any reasoning or logic on when I should keep something in the main table or to separate it? In addition to the tables I've posted so far I have.
<table border="0"><tbody><tr><th scope="col"><table border="1"><tbody><tr><th colspan="2">tblOperation</th> </tr> <tr> <th>ID

</th><th>Operation
</th></tr> <tr> <td>1</td> <td>Short Sweep Electric</td> </tr> <tr> <td>2</td> <td>Mechanical</td> </tr> <tr> <td>3</td> <td>Full Sweep Electric</td> </tr> <tr> <td>4</td> <td>Liquid Filled Mechanical</td> </tr> <tr> <td>5</td> <td>Digital</td> </tr> </tbody></table></th> <th scope="col"><table border="1"><tbody><tr><th colspan="2">tblPointer Color</th> </tr> <tr> <th>ID

</th><th>Pointer Color
</th></tr> <tr> <td>1</td> <td>Red</td> </tr> <tr> <td>2</td> <td>Black</td> </tr> <tr> <td>3</td> <td>White</td> </tr> <tr> <td>4</td> <td>Chrome</td> </tr> </tbody></table></th> <th scope="col"><table border="1"><tbody><tr><th colspan="2">tblScale</th> </tr> <tr> <th>ID

</th><th>Scale
</th></tr> <tr> <td>1</td> <td>PSI</td> </tr> <tr> <td>2</td> <td>Volts
</td> </tr> <tr> <td>3</td> <td>°C</td> </tr> <tr> <td>4</td> <td>°F</td> </tr> <tr> <td>5</td> <td>RPM</td> </tr> <tr> <td>6</td> <td>kg/cm^2</td> </tr> <tr> <td>7</td> <td>Bar</td> </tr> <tr> <td>8</td> <td>Hg/20PSI</td> </tr> </tbody></table></th> </tr> </tbody></table>
This is an example of a the information as I currently have it displaying.
<table border="1"><tbody><tr><th colspan="7">qBase Table</th> </tr> <tr> <th scope="col">Product code</th> <th scope="col">Face Color</th> <th scope="col">Bezel Color</th> <th scope="col">Pointer Color</th> <th scope="col">Dimension</th> <th scope="col">Operation</th> <th scope="col">Range</th> </tr> <tr> <td>A 1128</td> <td>Face: Black</td> <td>Bezel: Chrome</td> <td>Pointer: Red</td> <td>Gauge Size: 1-1/2" (38.1mm)</td> <td>Operation: Mechanical</td> <td>Calibration: 60-140 PSI</td> </tr> <tr> <td>A 1193</td> <td>Face: Black</td> <td>Bezel: Chrome</td> <td>Pointer: Red</td> <td>Gauge Size: 1-1/2" (38.1mm)</td> <td>Operation: Mechanical</td> <td>Calibration: 0-15 PSI</td> </tr> <tr> <td>A 3391</td> <td>Face: Black</td> <td>Bezel: Silver</td> <td>Pointer: Red</td> <td>Gauge Size: 2-1/16" (52.4mm)</td> <td>Operation: Short Sweep Electric</td> <td>Calibration: 8-18 Volts</td> </tr> </tbody></table>
My Base table holding the information
<table border="1" width="95%"> <tbody><tr> <th colspan="11">tblA1-Base Table</th> </tr> <tr> <th scope="col">ID</th> <th scope="col">Product code</th> <th scope="col">Bezel Color</th> <th scope="col">Face Color</th> <th scope="col">Pointer Color</th> <th scope="col">Dimension</th> <th scope="col">Operation</th> <th scope="col">Range</th> <th scope="col">Scale</th> <th scope="col">Operation</th> </tr> <tr> <td>1</td> <td>A 1128</td> <td>1</td> <td>3</td> <td>1</td> <td>2</td> <td>4</td> <td>1</td> <td>1</td> <td>2</td> </tr> <tr> <td>2</td> <td>A 1193</td> <td>1</td> <td>3</td> <td>1</td> <td>2</td> <td>5</td> <td>1</td> <td>1</td> <td>2</td> </tr> <tr> <td>179</td> <td>A 3391</td> <td>4</td> <td>3</td> <td>1</td> <td>1</td> <td>2</td> <td>2</td> <td>2</td> <td>1</td> </tr> </tbody></table>

SELECT [tblA1-Base Table].[Product Code], IIf([tblFace Color]![Face Color] Is Null,Null,"Face: " & [tblFace Color]![Face Color]) AS [Face Color], IIf([tblBezel Color]![Bezel Color] Is Null,Null,"Bezel: " & [tblBezel Color]![Bezel Color]) AS [Bezel Color], IIf([tblPointer Color]![Pointer Color] Is Null,Null,"Pointer: " & [tblPointer Color]![Pointer Color]) AS [Pointer Color], IIf([tblDimension]!Dimension Is Null,Null,"Gauge Size: " & [tblDimension]!Dimension) AS Dimension, IIf([tblOperation]![Operation] Is Null,Null,"Operation: " & [tblOperation]![Operation]) AS Operation, IIf([tblRange]![Range] Is Null Or [tblScale]![Scale (Units)] Is Null,Null,"Calibration: " & [tblRange]![Range] & " " & [tblScale]![Scale (Units)]) AS Range
FROM (((((([tblA1-Base Table] LEFT JOIN [tblBezel Color] ON [tblA1-Base Table].[Bezel Color] = [tblBezel Color].ID) LEFT JOIN tblDimension ON [tblA1-Base Table].Dimension = tblDimension.ID) LEFT JOIN [tblFace Color] ON [tblA1-Base Table].[Face Color] = [tblFace Color].ID) LEFT JOIN tblOperation ON [tblA1-Base Table].Operation = tblOperation.ID) LEFT JOIN tblRange ON [tblA1-Base Table].Range = tblRange.ID) LEFT JOIN [tblPointer Color] ON [tblA1-Base Table].[Pointer Color] = [tblPointer Color].ID) LEFT JOIN tblScale ON [tblA1-Base Table].Scale = tblScale.ID;

I have a misc table that has other bit of information for some products but am piecing that together. This will be the same format that I will be dealing with for each manufacturer. Each one has specific fields that apply only to each sector of the market their products apply to. Is there a better way for me to have my information set up? I've separated out them into their own tables since from time to time I'm requested to add or change things like remove the space before the scale IE 0-15PSI with no space for various reasons.

 

[Norie]

Sorry, again, I don't quite follow.

Are qBase and tblA1 actually tables?

Or is qBase the result of a query based on all the other tables?

 

[Norie]

Is this the sort of thing you want for the result?

<TABLE border=1 cellSpacing=0 bgColor=#ffffff>[FONT=Calibri][COLOR=#000000]<CAPTION>[B]qTable[/B]</CAPTION>[/COLOR][/FONT]<THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>[FONT=Calibri][COLOR=#000000]ProductCode[/COLOR][/FONT]</TH><TH bgColor=#c0c0c0 borderColor=#000000>[FONT=Calibri][COLOR=#000000]BezelColor[/COLOR][/FONT]</TH><TH bgColor=#c0c0c0 borderColor=#000000>[FONT=Calibri][COLOR=#000000]FaceColor[/COLOR][/FONT]</TH><TH bgColor=#c0c0c0 borderColor=#000000>[FONT=Calibri][COLOR=#000000]PointerColor[/COLOR][/FONT]</TH><TH bgColor=#c0c0c0 borderColor=#000000>[FONT=Calibri][COLOR=#000000]Dimension[/COLOR][/FONT]</TH><TH bgColor=#c0c0c0 borderColor=#000000>[FONT=Calibri][COLOR=#000000]Operation[/COLOR][/FONT]</TH><TH bgColor=#c0c0c0 borderColor=#000000>[FONT=Calibri][COLOR=#000000]Scale[/COLOR][/FONT]</TH><TH bgColor=#c0c0c0 borderColor=#000000>[FONT=Calibri][COLOR=#000000]HTMLList[/COLOR][/FONT]</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]A 1128[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Chrome[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Black[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Red[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]1-1/2" (38.1mm)[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Liquid Filled Mechanical[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]PSI[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Chrome
Black
Red
1-1/2" (38.1mm)
Liquid Filled Mechanical
PSI
[/COLOR][/FONT]</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]A 1193[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Chrome[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Black[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Red[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]1-1/2" (38.1mm)[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Digital[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]PSI[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Chrome
Black
Red
1-1/2" (38.1mm)
Digital
PSI
[/COLOR][/FONT]</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]A 3391[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Silver[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Black[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Red[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]2-1/16" (52.4mm)[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Mechanical[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Volts[/COLOR][/FONT]</TD><TD borderColor=#d0d7e5>[FONT=Calibri][COLOR=#000000]Silver
Black
Red
2-1/16" (52.4mm)
Mechanical
Volts
[/COLOR][/FONT]</TD></TR></TBODY><TFOOT></TFOOT></TABLE>
 
That's copied straight from Access, when you export as a CSV you get the HTML code rather than the rendered list.
 
[HTML]
"A 1128","Chrome","Black","Red","1-1/2"" (38.1mm)","Liquid Filled Mechanical","PSI","<li>Chrome</li><li>Black</li><li>Red</li><li>1-1/2"" (38.1mm)</li><li>Liquid Filled Mechanical</li><li>PSI</li>"
"A 1193","Chrome","Black","Red","1-1/2"" (38.1mm)","Digital","PSI","<li>Chrome</li><li>Black</li><li>Red</li><li>1-1/2"" (38.1mm)</li><li>Digital</li><li>PSI</li>"
"A 3391","Silver","Black","Red","2-1/16"" (52.4mm)","Mechanical","Volts","<li>Silver</li><li>Black</li><li>Red</li><li>2-1/16"" (52.4mm)</li><li>Mechanical</li><li>Volts</li>"
[/html]

 

[IvenBach]

qBase is a query based from tblA1. Sorry if its not as clear or organized as it should be. What you have is correct for the output in a similar format that I'd want the information to be generated as. Your code for the UDF works properly when I have it incorporated in a seperate query [qProductFeatures] that is pulling from [qBase Table]. When I try to have your htmlList function be incorporated into [qBase Table] That's when it's giving me the parameter request.

 

[Norie]

I know what the problem is.

You've used the same name, eg Bezel Color, in both tables.

What you have in tblA1 for Bezel Color isn't actually the colour, it's the ID of the colour.

So change the field name in tblA1 to something like BezelID.

If you do that for all the fields like that then you shouldn't have any problems.

By the way, is there supposed to be 2 fields called Operation?

 

[IvenBach]

Working like I originally wanted to. Thanks for that initial help. I've one last question since you mentioned my multiple tables. In your experience would it be better to have a single ID Table that would hold the information for each field and have everything based off of the an Autonumber primary key? I ask since my assumptions at times can lead to unnecessary work and time lost.

<table border="1"><tbody><tr><th colspan="5">tblKey_Table</th></tr><tr><th>ID
          </th><th>Bezel Color
          </th><th>Dimension
          </th><th>Face Color
          </th><th>Operation
     </th></tr><tr><td>1</td><td>Chrome</td><td>2-1/16" (52.4mm)</td><td>Beige</td><td>Short Sweep Electric</td></tr><tr><td>2</td><td>Gold</td><td>1-1/2" (38.1mm)</td><td>Silver</td><td>Mechanical</td></tr><tr><td>etc...</td><td>...</td><td>...</td><td>...</td><td>...
</td></tr> </tbody></table>

Thank you very much for all the back and forth and any blatant obvious things I've missed or missing.

Edit: If there were 2 fields for Operation it was most likely an error on my part.