Help with Database query

[paulbeckett]

Hi

Every product on my v7 site has a unique parent section holding it...

I'm trying to build an Access database query that allows me to display the properties contained in a product (specifically whether the product is in stock) and its parent section (specifically whether the section is hidden onsite or not).

I know I need the "Product" table, and I assume I also need the "Catalog section" table ... but how do I connect these two ... is it "NParentSectionID" or "Catalog reference" or something else?

I can't get a query to hold together. Any advice appreciated

Paul

[paulbeckett]

I know I need the "Product" table, and I assume I also need the "Catalog section" table ... but how do I connect these two ... is it "NParentSectionID" or "Catalog reference" or something else?

I think I have connected the two tables now (connecting SectionID to ParentSectionID) but my query is not working.

I am able to list all the products and values from the "product" table but I am also trying to pull out one value from the "Catalog Section" table. That is whether the parent section of the product is hidden on site? Does anyone know how I can do this?

Paul

[Mark H]

Hide on site field

Paul - it looks like the field for hide on site is field (column) "bHideOnWebSite" - this has 0 as not hidden, -1 as hidden.

[paulbeckett]

Paul - it looks like the field for hide on site is field (column) "bHideOnWebSite" - this has 0 as not hidden, -1 as hidden.

Thanks. Thats what I thought... I don't have the query in front of me right now but I seam to be getting 0 for every product when I know that some Catalog sections (parent sections) are definately hidden?

Paul

[Mark H]

I've just checked with one of our sites, the -1s in that field definitely tie up with those sections which are flagged hidden on site in Actinic. First thing to do is make the same check - look at the field contents to make sure some -1 are present. If they are but the query is not returning them, then you might have a problem with the query.

[completerookie]

always write the query first without the exclusions, run the query and check that you get ALL results, look for a product that you know is hidden on the web, and check the value in the column,
then adjust the query to add the if condition and rr-run the query.

[paulbeckett]

I've just checked with one of our sites, the -1s in that field definitely tie up with those sections which are flagged hidden on site in Actinic

Hi Mark, sounds like you are doing something similar to want I want. I will have a look at my query later but I also wondered if your opinion about this...

As you are aware I want to: display the stock level of products on my site and the corresponding parent sections hidden status. I am sure this is do-able as discussed above. This is so I can hide products that are out of stock.

The only thing is... for every product I sell, my entire site has the original product (contained in its own parent section) grouped "By Brand" but also at least one duplicate product (contained in its own parent section). I'm not sure how I display both original and duplicates.

When I look down the Product table the original products have a Product Reference of 000345 (example) and the duplicates have something like 1!!000345. Do you have anything set-up like this? If so, how do you pull out your duplicates?

[Mark H]

Hide out of stock items

Paul - I don't get on with duplicates and have never used them - in fact we feed our websites from an externally linked Excel worksheet, and simply create "duplicates" by copying rows in that worksheet and changing the text/ref a bit. Anyway, enough about us.......

I haven't done what you are trying to do, so I will have to hope that someone else comes along - I simply had a peek in the database this morning to help you with your question!

PS are you trying to hide out of stock items because you can't get new stock quickly? We can get new stock in about 10 days and simply leave out of stock items on the site with an automatic message saying "more on the way please allow 14 days" and lots of people still buy them.

[paulbeckett]

PS are you trying to hide out of stock items because you can't get new stock quickly? We can get new stock in about 10 days and simply leave out of stock items on the site with an automatic message saying "more on the way please allow 14 days" and lots of people still buy them.

Yes we have items that have quite long lead times on for restocking and therefore need to hide them (and their parent sections) from the site.

Paul