What do the field names of customnav properties appear as in Access?

[paulbeckett]

Hello

For every product, I have alot of custom properties set-up (e.g. EXINFOMADEIN which allows me to enter where the product is made).

I have been trawling through my database looking for these fields but cant find them. I assume that they are not known by the property name I give them (e.g. EXINFOMADEIN) and instead something like sString1? Is this correct?

Also - what table would I find them in - I had presumed ProductProperties?

Many thanks

Paul

[Kermy]

The CUSTOMVAR names are held in HTMLVariables.

The actual values of each CUSTOMVAR are in ProductProperties - the 'sString1' column. The 'nValue1' column corresponds to the ID assigned to the CUSTOMVAR name in HTMLVariables.

[paulbeckett]

Hmm...so the names of the customproperties are html but the data is held in the database under sString1, sString2, etc?

Therefore I should be able to create a database of products that displays the information held in my custom navs?

Paul

[Kermy]

Sorry, I should explain that better.

The CUSTOMVAR names are held in the database table called 'HTMLVariables'. That table stores each CUSTOMVAR name (in the 'sName' column), and assigns each name with a unique ID (the 'nVariableID' column).

The CUSTOMVAR values that you assign to your products are held in the 'ProductProperties' table.

- The 'sProductRef' column denotes the product the CUSTOMVAR is assigned to
- The 'sString1' column is the actual CUSTOMVAR value
- The 'nValue1' column contains a value from the 'nVariableID' column in the HTMLVariables table

This is how the two tables are linked together, allowing you to assign many properties to the same CUSTOMVAR name.

[paulbeckett]

Using Access to query v7 database

Hi all

I am trying to create a query using Access to allow me to list my products and all their custom navs.

I have created a simple query which presents the product name, product reference, price, etc. However it presents multiple records for each product (see screen shot attached - kjbeckett-query.jpg). The amount of duplicate records seems to correspond to the amount of string1 (e.g. customnavs) a product has.

I cant figure out how to display one record per product?

Furthermore I have created this sql that diplays customnavs:

Product Type: IIf([nValue1]=31,[ProductProperties].[sString1])

(for your info - nValue1 31 is Brand name). I have then repeated this sql in different columns (changing the nValue value) to display all the custom navs. However they are listed on multiple records (see screen shot attached - kjbeckett-query.jpg)?

Does anyone know how to list one product as one record, with all the nValues on the same line?

Kind regards

Paul
KJ Beckett

[Bruce]

Paul,

Checking this one for you.

Kind regards,

[paulbeckett]

Thanks Bruce.

I am very inexperienced with Access but I am continuing to trial things - nothing has worked yet. I have tried doing SELECT DISTINCT queries etc but nothing has quite worked.

Any suggestions would be welcome.

Kind regards

Paul
KJ Beckett

[pnagames]

Thanks Bruce.

I am very inexperienced with Access but I am continuing to trial things - nothing has worked yet. I have tried doing SELECT DISTINCT queries etc but nothing has quite worked.

Any suggestions would be welcome.

Kind regards

Paul
KJ Beckett

Morning Paul

can you please post a screenshot of your query. I need to see the relationships that you have in there...

[Jan]

You need to join one instance of the product property table into your query for each customvar, the examples below work for 3 customvars

This will give you results for records with all of the customvars set

SELECT Product.[Product reference], Product.[Short description],
ProductProperties.sString1, ProductProperties_1.sString1,
ProductProperties_2.sString1
FROM ((Product INNER JOIN ProductProperties ON Product.[Product reference]
= ProductProperties.sProductRef) INNER JOIN ProductProperties AS
ProductProperties_1 ON Product.[Product reference] =
ProductProperties_1.sProductRef) INNER JOIN ProductProperties AS
ProductProperties_2 ON Product.[Product reference] =
ProductProperties_2.sProductRef
WHERE (((ProductProperties.nValue1)=5) AND
((ProductProperties_1.nValue1)=6) AND ((ProductProperties_2.nValue1)=7)
AND ((ProductProperties.nType)=3) AND ((ProductProperties_1.nType)=3) AND
((ProductProperties_2.nType)=3));


This will give you a row for every product, showing which ever values are
set :

SELECT Product.[Product reference], Product.[Short description],
ProductProperties.sString1, ProductProperties_1.sString1,
ProductProperties_2.sString1
FROM ((Product LEFT JOIN ProductProperties ON Product.[Product reference]
= ProductProperties.sProductRef) LEFT JOIN ProductProperties AS
ProductProperties_1 ON Product.[Product reference] =
ProductProperties_1.sProductRef) LEFT JOIN ProductProperties AS
ProductProperties_2 ON Product.[Product reference] =
ProductProperties_2.sProductRef
WHERE (((ProductProperties.nValue1)=5) AND
((ProductProperties_1.nValue1)=6) AND ((ProductProperties_2.nValue1)=7)
AND ((ProductProperties.nType)=3) AND ((ProductProperties_1.nType)=3) AND
((ProductProperties_2.nType)=3)) OR (((ProductProperties.nValue1) Is Null)
AND ((ProductProperties_1.nValue1) Is Null) AND
((ProductProperties_2.nValue1) Is Null) AND ((ProductProperties.nType) Is
Null) AND ((ProductProperties_1.nType) Is Null) AND
((ProductProperties_2.nType) Is Null));

Don't be scared off by the SQL, make a new query in access, change the view to SQL view, paste one of the queries in and then you can change to design view and work as usual.

Regards,

[completerookie]

there is alternative methods that handle the sql using PivotTables ( or CrossTabs ) - not often used, but very very powerful

firstly use a bit of throwaway SQL to find all the CustomVars you want to show on your report using the following SQL code

SELECT HTMLVariables.nVariableID, HTMLVariables.sName
FROM HTMLVariables
WITH OWNERACCESS OPTION;

Make a note of the numbers in the 1st column. - in my particular case, the CustomVars were types 9,12,13,16,17

Now create a second query called "qryFindAllCustomVars" - switch to SqlView on the VIEW menu and enter the following:

SELECT HTMLVariables.nVariableID, HTMLVariables.sName,
 ProductProperties.sProductRef, ProductProperties.sString1,
 Product.[Short description]
FROM Product INNER JOIN (ProductProperties 
  INNER JOIN 
  HTMLVariables ON ProductProperties.nValue1 = TMLVariables.nVariableID) 
  ON Product.[Product reference] = ProductProperties.sProductRef
  WHERE (((HTMLVariables.nVariableID)=9
    Or (HTMLVariables.nVariableID)=12
    Or (HTMLVariables.nVariableID)=13
    Or (HTMLVariables.nVariableID)=17
    Or (HTMLVariables.nVariableID)=16))

I've laid out the code this way so you can see the WHERE clause easily - you can ammend the WHERE clause to suit your circumstances

run the query and it should tell you if you have the correct values coming back - if not, adjust the query to make it right

this will show you

• the CustomVar number used in the WHERE clause
• the name of the CustomVar
• the ProductRef
• the value of the CustomVar
• the main description of the product

save this as "qryFindAllCustomVars"
now you need create a second query called "qryFindAllCustomVars_Crosstab" and enter the following code:

TRANSFORM First(qryFindAllCustomVars.sString1) AS FirstOfsString1
SELECT qryFindAllCustomVars.sProductRef
FROM qryFindAllCustomVars
GROUP BY qryFindAllCustomVars.sProductRef
PIVOT qryFindAllCustomVars.sName;

this then gives you the

• Product Code in the 1st column
• each of the required CustomVars in the other column-titles
• and each of the values in the actual table

so you end up with a nice little table of you Product-CustomVars

Ref	 SupCode  Supplier  LeadTime  MinQty  SupplierPrice
----------------------------------------------------------------------
Test1     X123er     Maplins   3wk         4          12.60
Test2     ABC123     BandQ     4d          1           3.40

By changing the WHERE clause in the first query, you will get more (or less) columns in the second query

its now a simple exercise to turn this into a report.

I am NOT saying that Jans solution does not work, it does and works well, but as with many things, "theres many ways to skin a cat"

regards

kev

[paulbeckett]

Jan, Kev

Many thanks for your advice.

I am playing with Jan's 1st suggestion first, which seems to do exactly what I want it to do. Many thanks.

I'm not sure how to add additional columns for additional customvars. I have tried copying the SQL I think needs to be replicated and adding to the SQL using the same formating, for instance creating a ProductProperties_3 for the 4th customvar, however this is throwing up syntax errors. Can you indicate how to increase the number of customvars?

Kind regards

Paul
KJ Beckett

[paulbeckett]

Hi

This is the SQL I have created in order to add a 4th customvar but it is coming up with the error...

Syntax error (missing operator) in query expression Product.[Product reference] = ProductProperties_2.sProductRef INNER JOIN ProductProperties AS ProductProperties_3 ON Product.[Product reference] = ProductProperties_3.sProductRef


SELECT Product.[Product reference], Product.[Short description], ProductProperties.sString1, ProductProperties_1.sString1, ProductProperties_2.sString1, ProductProperties_3.sString1, "http://www.kjbeckett.com/acatalog/"+Mid([Image FileName],InstrR([Image Filename],"\")+1) AS [Image]
FROM ((Product INNER JOIN ProductProperties ON Product.[Product reference] = ProductProperties.sProductRef) INNER JOIN ProductProperties AS ProductProperties_1 ON Product.[Product reference] = ProductProperties_1.sProductRef) INNER JOIN ProductProperties AS ProductProperties_2 ON Product.[Product reference] = ProductProperties_2.sProductRef INNER JOIN ProductProperties AS ProductProperties_3 ON Product.[Product reference] = ProductProperties_3.sProductRef
WHERE (((ProductProperties.nValue1)=30) AND ((ProductProperties_1.nValue1)=31) AND ((ProductProperties_2.nValue1)=32) AND ((ProductProperties_3.nValue1)=55) AND ((ProductProperties.nType)=3) AND ((ProductProperties_1.nType)=3) AND ((ProductProperties_2.nType)=3) AND ((ProductProperties_3.nType)=3));

I'm not sure what missing operator I have left out?

Paul

[Jan]

Try this Paul.

SELECT Product.[Product reference], Product.[Short description], ProductProperties.sString1, ProductProperties_1.sString1, ProductProperties_2.sString1, ProductProperties_3.sString1, "http://www.kjbeckett.com/acatalog/"+Mid([Image FileName],Instr([Image Filename],"\")+1) AS [Image]
FROM (((Product INNER JOIN ProductProperties ON Product.[Product reference] = ProductProperties.sProductRef) INNER JOIN ProductProperties AS ProductProperties_1 ON Product.[Product reference] = ProductProperties_1.sProductRef) INNER JOIN ProductProperties AS ProductProperties_2 ON Product.[Product reference] = ProductProperties_2.sProductRef) INNER JOIN ProductProperties AS ProductProperties_3 ON Product.[Product reference] = ProductProperties_3.sProductRef
WHERE (((ProductProperties.nValue1)=30) AND ((ProductProperties_1.nValue1)=31) AND ((ProductProperties_2.nValue1)=32) AND ((ProductProperties.nType)=3) AND ((ProductProperties_1.nType)=3) AND ((ProductProperties_2.nType)=3) AND ((ProductProperties_3.nType)=3) AND ((ProductProperties_3.nValue1)=55));

Regards,

[paulbeckett]

Jan

I understand it now! Many thanks for your guidance.

Paul
KJ Beckett

[paulbeckett]

Hello

Arrgh! Stuck again - and I can see no reason why!

Following the messages below I have created a list that shows many of my products details including 6 product customvars.

I have now tried to include a 7th customvar and my list only shows the products that has information contained in that customvar.

I have used the exact same method of setting up the query as I did for the other 6 customvars (e.g. I built a new ProductProperties_7 table) - many of them dont have information in but the query still demonstrated the full list (but left blanks where there was no information).

This is the code with 6 customvars where the full product list is returned:

SELECT Product.[Product reference] AS Ref, Product.[Short description] AS [Product Name], ProductProperties.sString1 AS Brand, ProductProperties_1.sString1 AS Type, ProductProperties_2.sString1 AS [Made In], ProductProperties_3.sString1 AS Material, Format(IIf([sTax1OpaqueData]="101=1750.00=0=" Or [sTax1OpaqueData]="101=1750=0=",([Product].[price]/100)*1.175,[product].[price]/100),"Fixed") AS [NET Price], ProductProperties_4.sString1 AS Colour, ProductProperties_5.sString1 AS [NET cost], ProductProperties_6.sString1 AS [Supplier ref], Left(myReplace(myReplace([Full Description],'• ',""),Chr(13) & Chr(10)," "),+400) AS [Description/Features], Product.nStockOnHand AS [Stock Level]
FROM [Catalog section] INNER JOIN (((((((Product INNER JOIN ProductProperties ON Product.[Product reference] = ProductProperties.sProductRef) INNER JOIN ProductProperties AS ProductProperties_1 ON Product.[Product reference] = ProductProperties_1.sProductRef) INNER JOIN ProductProperties AS ProductProperties_2 ON Product.[Product reference] = ProductProperties_2.sProductRef) INNER JOIN ProductProperties AS ProductProperties_3 ON Product.[Product reference] = ProductProperties_3.sProductRef) INNER JOIN ProductProperties AS ProductProperties_4 ON Product.[Product reference] = ProductProperties_4.sProductRef) INNER JOIN ProductProperties AS ProductProperties_5 ON Product.[Product reference] = ProductProperties_5.sProductRef) INNER JOIN ProductProperties AS ProductProperties_6 ON Product.[Product reference] = ProductProperties_6.sProductRef) ON [Catalog section].nSectionID = Product.nParentSectionID
WHERE (((ProductProperties.nValue1)=30) AND ((ProductProperties_1.nValue1)=31) AND ((ProductProperties_2.nValue1)=32) AND ((ProductProperties_3.nValue1)=29) AND ((ProductProperties_1.nType)=3) AND ((ProductProperties_2.nType)=3) AND ((ProductProperties_3.nType)=3) AND ((ProductProperties_4.nValue1)=28) AND ((ProductProperties_4.nType)=3) AND ((ProductProperties_5.nValue1)=54) AND ((ProductProperties_5.nType)=3) AND ((ProductProperties_6.nValue1)=53) AND ((ProductProperties_6.nType)=3) AND (([Catalog section].bExcludeFromFroogle)=0));

and this is the SQL code where I have tried to add the 7th customvar but only the products with information in the new customvar are being returned.

SELECT Product.[Product reference] AS Ref, Product.[Short description] AS [Product Name], ProductProperties.sString1 AS Brand, ProductProperties_1.sString1 AS Type, ProductProperties_2.sString1 AS [Made In], ProductProperties_3.sString1 AS Material, Format(IIf([sTax1OpaqueData]="101=1750.00=0=" Or [sTax1OpaqueData]="101=1750=0=",([Product].[price]/100)*1.175,[product].[price]/100),"Fixed") AS [NET Price], ProductProperties_4.sString1 AS Colour, ProductProperties_5.sString1 AS [NET cost], ProductProperties_6.sString1 AS [Supplier ref], Left(myReplace(myReplace([Full Description],'• ',""),Chr(13) & Chr(10)," "),+400) AS [Description/Features], Product.nStockOnHand AS [Stock Level], ProductProperties_7.sString1
FROM ([Catalog section] INNER JOIN (((((((Product INNER JOIN ProductProperties ON Product.[Product reference] = ProductProperties.sProductRef) INNER JOIN ProductProperties AS ProductProperties_1 ON Product.[Product reference] = ProductProperties_1.sProductRef) INNER JOIN ProductProperties AS ProductProperties_2 ON Product.[Product reference] = ProductProperties_2.sProductRef) INNER JOIN ProductProperties AS ProductProperties_3 ON Product.[Product reference] = ProductProperties_3.sProductRef) INNER JOIN ProductProperties AS ProductProperties_4 ON Product.[Product reference] = ProductProperties_4.sProductRef) INNER JOIN ProductProperties AS ProductProperties_5 ON Product.[Product reference] = ProductProperties_5.sProductRef) INNER JOIN ProductProperties AS ProductProperties_6 ON Product.[Product reference] = ProductProperties_6.sProductRef) ON [Catalog section].nSectionID = Product.nParentSectionID) INNER JOIN ProductProperties AS ProductProperties_7 ON Product.[Product reference] = ProductProperties_7.sProductRef
WHERE (((ProductProperties.nValue1)=30) AND ((ProductProperties_1.nValue1)=31) AND ((ProductProperties_2.nValue1)=32) AND ((ProductProperties_3.nValue1)=29) AND ((ProductProperties_1.nType)=3) AND ((ProductProperties_2.nType)=3) AND ((ProductProperties_3.nType)=3) AND ((ProductProperties_4.nValue1)=28) AND ((ProductProperties_4.nType)=3) AND ((ProductProperties_5.nValue1)=54) AND ((ProductProperties_5.nType)=3) AND ((ProductProperties_6.nValue1)=53) AND ((ProductProperties_6.nType)=3) AND (([Catalog section].bExcludeFromFroogle)=0) AND ((ProductProperties_7.nType)=3) AND ((ProductProperties_7.nValue1)=81));

Can any one advise me what I have done wrong?

Kind regards

Paul