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

[Jan]

If you want to include products that do not have all of the customvars defined, you need use the second format, so change the inner join to left join and you will get an entry for all products, whether they have the customvars defined or not.

Personally I think you would be better using Kev's SQL, it looks neater to me, particularly as you have so many customvars.

I am assuming that his SQL will return rows whether the customvars are present of not, haven't tried it though.

Regards,

[paulbeckett]

Hi Jan

Oh, I have been using the first format with success and not all customvars have information in. For the other customvars where there is no information, the query has left blank spaces?

It is only the latest two customvars (which I created yesterday) that have caused this problem. I suppose the only two difference between these new customvar and the others are 1) they were created after I created the query 2) most of the customvars are blank.

Paul

[Jan]

If you do an inner join, only rows that have matches in all tables are returned, I think that you have probably been loosing a few rows anyway, but the latest customvars have more missing and so made it obvious what was happening.

Regards,

[paulbeckett]

Hi Jan

Yes - your right! I had been dropping products and not realising it! Some of my products had customnavs on but with no information in - this was returning blanks. Others didnt have all the customnavs - these were missing!

Thanks

Paul

[completerookie]

One of the problems you start to encounter is that the SQL code is starting to look very complex - consider splitting the SQL into two (or more) entirely separate queries. - this way, you can concentrate on just the problem and make small changes to see if that gives the result. - also, don't be afraid of making a copy of the information and experimenting until the sql works, then copy the working code into the original query.

• query One returns JUST the ProductReference and ALL the CustomVarInformation - call this qryCustomVars
• query Two returns the results from the 1st query together with all the other bits of information. - call this qryCustomVarsAndOtherInfo

When you create the second query, just join the [Product reference] in the [Product] table with the [sProductRef] of the qryCustomVars

By doing this, you can run the 1st query to test the return of the CustomVar information, and then test the 2nd query to test the return of all the other information.

One other (extremely beneficial) reason to split the query into two, is that any time you want to make a new (bigger) query returning slightly different info, all you do is include the qryCustomVars to do all the CustomVar work. - any time you want to add another CustomVar, you only edit one query, and the information is instantly available to all the other queries.