Access product catalogue website style

[MajP]

Something's wrong.
View attachment 118019
I can see the name of the file, but it does not let me download it and that error message shows up. I suggest you use another service, like Google Drive or even Dropbox.

I was able to get it originally.

 

[Edgar_]

I was able to get it originally.

I was copy-pasting from Google's OCR but it didn't read it correctly, when I typed it myself, it worked. OP translated database and it's attached.

@Faoineag
We're in post #42, the form design has been covered by now, I think you should focus on the database schema that works best for you, I suggest another thread for that, as this can be done in multiple ways.

I'm just facilitating these resources for others to help you as I've run out of time this week.

 

[MajP]

@Faoineag,
You are making the exact same mistake in carrying to many foreign keys. See this discussion it is almost identical.

Solved - Normalization - Struggles In Access

New to db design & really struggling with it. Watched countless tutorials; implementing seems far harder. Been told to avoid composite keys in Access as it makes hard work, which seems to cause violations but I really don't understand normalization. ResTypesT - Labour/ Plant/ Material/...

www.access-programmers.co.uk

A subcategory is related to a category. Therefore if I have a FK to a subcategory I know the category. I do not need to select the category in other data tables. I describe in that thread why people do that, but it is problematic.
The reason people do this is because they want to have cascading comboboxes on the form. There are better solutions that do not require carrying an extra FK. Not a big problem in a small DB, but a huge problem in bigger dbs.

You can no longer ensure referential integrity.

TblManufacturer
--ManufacturerID
--Manufacturer

tblCarModels
--ModelID
--ModelName
--ManufacturerID_FK

If in my data table I simpy store ModelID, I know the manufacturer through the relation between the two tables no need to also store the manufacturer ID.

What is the Problem? Besides extra work you can not ensure you create something like a
Mercedes Civic
Honda Roma Spider

By adding both keys. If you only add the model FK you always will have the proper Manufacturer.

 

[Faoineag]

I uploaded the db to Dropbox at this link. I am mortified that I am wasting your time, this system of attaching links is an absurd further waste of time. Sorry.
The one attached is an extract of the database with which I run my business. The main mask is the ‘Product detail’, which is a kind of product sheet, where each item is filed, i.e. it is placed in a series of categories and subcategories. essentially there are four: ‘Category’. , ‘Supplier’, ‘Activity’ and ‘SubCategory’, they are four boxes combined in a cascade; then there is a further classification which is obtained by clicking on the ‘product detail sheet’ button, clicking it opens another mask where I can enter another type of characteristic which is the ‘Product Type’. What I'd like to do is a thumbnail view (like the one Edgar made, i.e. ‘prodcts webstyle ie6’ without registry key changes) of the products filtered through the four major categories plus ‘Product Type’.
I will now study the other proposed solutions that arrived before I wrote this post. First of all Thank you to those who are helping me

 

[MajP]

Here is a demo using a temp table and your data and image controls. I only did 3 columns but you could repeat to as many as you want.

with sub category

 

[MajP]

The code first creates a query def sql so you can filter the records. It then loops the filtered records and puts the values into the temp table.

Here is the basic code to do that.

Public Sub CreateCatalog(Optional CatID As Long = 0, Optional subCatID As Long = 0, Optional ActivityID As Long = 0)
  Dim strSql As String
  Dim qdf As QueryDef
  Dim fltr As String
 
  strSql = "SELECT Product, ImagePath FROM tbProducts "
  Set qdf = CurrentDb.QueryDefs("qryTempCatalogFilter")
 
  If CatID <> 0 Then fltr = "Category = " & CatID
  If subCatID <> 0 Then
      fltr = " Subcategory = " & subCatID
  End If
  If ActivityID <> 0 Then
    If CatID = 0 And subCatID = 0 Then
       fltr = "Activity = " & ActivityID
    Else
       fltr = fltr & " AND Acitivity = " & ActivityID
    End If
  End If
 
  If fltr <> "" Then
    strSql = strSql & " WHERE " & fltr
  End If
  qdf.SQL = strSql & " Order By Category, Subcategory, Product"
 ' Debug.Print qdf.SQL
  CreateTempTable
  'currentdb.TableDefs.Refresh
End Sub
Public Sub CreateTempTable()
  Dim PathLead As String
  Dim rsTemp As DAO.Recordset
  Dim rsSource As DAO.Recordset
  Dim i As Integer
  Dim Path As String
 
  Set rsTemp = CurrentDb.OpenRecordset("tblTempCatalog")
  Set rsSource = CurrentDb.OpenRecordset("qryTempCatalogFilter")
  PathLead = CurrentProject.Path & "\img\"
  CurrentDb.Execute "qryClearTempCatalog"
  Do While Not rsSource.EOF
      rsTemp.AddNew
      
        rsTemp.Fields("ImagePath1") = PathLead & rsSource!ImagePath
        rsTemp.Fields("ImageName1") = PlainText(rsSource!Product)
        If Not rsSource.EOF Then rsSource.MoveNext
        If Not rsSource.EOF Then
          rsTemp.Fields("ImagePath2") = PathLead & rsSource!ImagePath
          rsTemp.Fields("ImageName2") = PlainText(rsSource!Product)
        End If
        If Not rsSource.EOF Then rsSource.MoveNext
        If Not rsSource.EOF Then
          rsTemp.Fields("ImagePath3") = PathLead & rsSource!ImagePath
          rsTemp.Fields("ImageName3") = PlainText(rsSource!Product)
        End If
      
      rsTemp.Update
 
    If Not rsSource.EOF Then rsSource.MoveNext
  Loop
 
End Sub

I called the method CreateTempTable but it should be called FillTempTable, because the temp table exists and you are just filling it like a grid.

 

[Faoineag]

The code first creates a query def sql so you can filter the records. It then loops the filtered records and puts the values into the temp table.

Here is the basic code to do that.

Public Sub CreateCatalog(Optional CatID As Long = 0, Optional subCatID As Long = 0, Optional ActivityID As Long = 0)
  Dim strSql As String
  Dim qdf As QueryDef
  Dim fltr As String
 
  strSql = "SELECT Product, ImagePath FROM tbProducts "
  Set qdf = CurrentDb.QueryDefs("qryTempCatalogFilter")
 
  If CatID <> 0 Then fltr = "Category = " & CatID
  If subCatID <> 0 Then
      fltr = " Subcategory = " & subCatID
  End If
  If ActivityID <> 0 Then
    If CatID = 0 And subCatID = 0 Then
       fltr = "Activity = " & ActivityID
    Else
       fltr = fltr & " AND Acitivity = " & ActivityID
    End If
  End If
 
  If fltr <> "" Then
    strSql = strSql & " WHERE " & fltr
  End If
  qdf.SQL = strSql & " Order By Category, Subcategory, Product"
 ' Debug.Print qdf.SQL
  CreateTempTable
  'currentdb.TableDefs.Refresh
End Sub
Public Sub CreateTempTable()
  Dim PathLead As String
  Dim rsTemp As DAO.Recordset
  Dim rsSource As DAO.Recordset
  Dim i As Integer
  Dim Path As String
 
  Set rsTemp = CurrentDb.OpenRecordset("tblTempCatalog")
  Set rsSource = CurrentDb.OpenRecordset("qryTempCatalogFilter")
  PathLead = CurrentProject.Path & "\img\"
  CurrentDb.Execute "qryClearTempCatalog"
  Do While Not rsSource.EOF
      rsTemp.AddNew
     
        rsTemp.Fields("ImagePath1") = PathLead & rsSource!ImagePath
        rsTemp.Fields("ImageName1") = PlainText(rsSource!Product)
        If Not rsSource.EOF Then rsSource.MoveNext
        If Not rsSource.EOF Then
          rsTemp.Fields("ImagePath2") = PathLead & rsSource!ImagePath
          rsTemp.Fields("ImageName2") = PlainText(rsSource!Product)
        End If
        If Not rsSource.EOF Then rsSource.MoveNext
        If Not rsSource.EOF Then
          rsTemp.Fields("ImagePath3") = PathLead & rsSource!ImagePath
          rsTemp.Fields("ImageName3") = PlainText(rsSource!Product)
        End If
     
      rsTemp.Update
 
    If Not rsSource.EOF Then rsSource.MoveNext
  Loop
 
End Sub

I called the method CreateTempTable but it should be called FillTempTable, because the temp table exists and you are just filling it like a grid.

Thank you very much MajP. Right now I'm studying your instructions in the thread you pointed out to me to figure out how to correct normalisation errors. Maybe I'll create another thread? Thanks again

 

[MajP]

This may be one of those cases where you could get nicer results from an unbound form. Assume you want to display at max a 5 x 5 grid so as to fit nicely on the screen and the user does not have to scroll. They would select "next 25 Records" to move to the next group of records.

Then the first part is the same, where you create the query definition filtered to the records. Then you read the source and push it to a fix set of unbound controls not to a temp table.
The complication to this is that you need at the bottom of the form something that shows what group of records are displayed ("displaying 26-50 of 212") and buttons like "show previous 25 records", "show next 25 records."

This all assumes you cannot get the web browser to support your needs which provides a flexible and nice view.

 

[Faoineag]

Maybe I am wrong, but I thought I would not open another thread in view of the difficulty in inserting the example db. After reading the thread suggested by MajP, I tried editing the tables. I enclose a diagram showing the contents of the tables and a hypothesis of the new table setup, up to the error indicated. Where am I going wrong?

 

[MajP]

What you are showing is more complicated than what I was thinking.
The relatiohship between Categories and SubCategories is a One to Many
One category can be related to many sub categories, but a Sub category can only relate to one category

Sports Equipment
-- Volleyball nets
-- Cleats
-- etc
Sports Clothes
-- Shorts

But what you are showing in activities is a many to Many.
A category can relate to many activities and an Activity can relate to many categories

Sports Equipement
-- Volleyball
-- Basketball
-- Football
SPorts Clothing
-- Volleyball
--Basketball
--Footbal

So this will get a little tricky IMO. You can create a many to many by adding another table but in my opinion not worth it for you. I would add detail to Activity removing the may to may by adding more records.

Category:
-- Sports Equipment
-- Sports Cloths
Activity
-- Volleyball Equipment (to sports equipement)
-- Volleyball Clothes (to sports clothes)
-- Football Equipment
-- Football Clothes
....

Sub Cats
-- Volleyball Nets (to equip)
-- Volleyball Shoes (to V clothes)

The same problem could arise if you have a Generic pair of sports shoes. They can be used in basketball, volleyball, tennis. You could come up with a very complicated many to many relationship or just build a subcategory "multi sport shoes"

 

[Faoineag]

In a certain sense I am comforted by what you say because the idea of making such a big change on a huge database (I have about 7600 products) frightened me a lot given my scarce skills, I was afraid of throwing away years of work. However, since I would like to be sure that I have understood correctly, I would like to ask you if, when you have the time and desire, you could make the changes that you think would be necessary to make the structure of the database correct, acting directly on the last database that I have attached and which I reproduce here. I know that I am asking a lot and if I ask too much, I am willing to acknowledge the possible fee. Thank you very much for the time you have already given me

 

[MajP]

One thing that I think can cause lots of problems is you have the following in several tables. It would confuse me a lot.

tbSupplier
--idSupplier (ID)
--Supplier (Name of supplier)

in
tbProducts
--Supplier (is a numeric foreign key using the same name as the Supplier Name field in the related table)

1. Having two fields in different tables with the same name is not good.
2. Having two fields in different tables with the same name and different data types meaning different things is really bad.
3. Having two fields in different tables with the same name and different data types meaning different things using a table look up is absolutely horrendous.

You are doing 3 in several places.

This will confuse you and anyone you ask to look at the db.

I always do this in my naming
if the PK is IdSupplier
in any related table I have
idSupplier_FK (I immediately know it is an FK)

If you have name autocorrect on you should be able to change those foreign key names and fix it in forms and queries.

I renamed the fields and removed the table lookups. We have to discuss how you want to use Categories, Activities, and Subcategories.
You may want many to many. You kind of have a junction table. But some of the foreign keys make no sense.

This is my change to the naming

To

 

[MajP]

I will asks questions one at a time.
A supplier can supply lots of goods I would think in different categories. Currently in the supplier table you have foreign keys to Category, Activity, SubCategory.
You have a junction table tbCategorySupplier which makes sense. That supplier makes different categories of things. Now you can filter on that supplier when assigning to a product based on category.

However, having ActivityID_FK and SubCategory_FK makes no sense in the supplier table. Are you thinking you need to filter more.

If so the junction table would need to likely be tbSubCategoriesSupplier
So instead of filtering on sports equipment you can filter on more detail "volley ball nets". Just depends what you want to do.

 

[Mark_]

Special note regarding setting up a Many to Many relationship MajP touched on; The time and effort required to properly enter data for the relationships is often far greater than the time required to code. With 7600 products this means for EACH item you need to decide if it makes sense to have it attached to EACH category. This is far harder than the coding in many cases. Too many links is as useless as too few.

You may want to look at it down the line. I'd look to customer feedback to see if it is worth the data entry required.

 

[Faoineag]

I will asks questions one at a time.
A supplier can supply lots of goods I would think in different categories. Currently in the supplier table you have foreign keys to Category, Activity, SubCategory.
You have a junction table tbCategorySupplier which makes sense. That supplier makes different categories of things. Now you can filter on that supplier when assigning to a product based on category.

However, having ActivityID_FK and SubCategory_FK makes no sense in the supplier table. Are you thinking you need to filter more.

If so the junction table would need to likely be tbSubCategoriesSupplier
So instead of filtering on sports equipment you can filter on more detail "volley ball nets". Just depends what you want to do.

Thanks MajP for helping me try to trace the error back to Access abc. Looking at your screenshots I wondered why you leave the tbProductsSubCategories table ‘isolated’, when I created it (with the intention of creating a join table) it had seemed the most correct choice to hold together the many-to-many relationships I seemed to identify in my settings. After your posts I began to doubt that these are indeed many-to-many relationships and reviewed the whole thing theoretically, this can be summarised as follows:
One Category can have many Suppliers and one Supplier can have many Categories.
A Supplier can have many Activities and an Activity can have many Suppliers.
A Supplier can have many SubCategories (because it has many Activities)
A Category can have many Activities and an Activity can have many Categories.
A SubCategory can have one Category (example: the volleyball net can only belong to the Sports Equipment Category, not to Clothing), many Activities and many Suppliers. Apart from the fact that I still have to assimilate well the FK keys and everything explained in the thread on normalisation you suggested yesterday, what gets me into a crisis is the management of the relationships to which tbSubCategories belong and ultimately the relationship between tbProducts and tbSubCategories because the same product can belong to many Suppliers, many Activities and therefore many SubCategories.

 

[Faoineag]

Nota speciale riguardante l'istituzione di una relazione Molti a Molti di cui MajP ha parlato; Il tempo e l'impegno necessari per immettere correttamente i dati per le relazioni sono spesso di gran lunga superiori al tempo necessario per la codifica. Con 7600 prodotti questo significa che per OGNI articolo devi decidere se ha senso averlo attaccato a OGNI categoria. In molti casi questo è molto più difficile della codifica. Troppi link sono inutili quanto troppo pochi.

Potresti voler dare un'occhiata a tutto il resto. Guarderei il feedback dei clienti per vedere se vale la pena inserire i dati richiesti.

Soprattutto, ho il terrore di modificare una struttura che sembra funzionare perché ho paura di non sapere come gestire le conseguenze di questi cambiamenti. Utilizzo solo questo database, con esso gestisco i prodotti, il catalogo (infatti la richiesta della visualizzazione dell'immagine con cui ho aperto questo thread era proprio per avere una soluzione più snella per la visualizzazione delle porzioni filtrate di questo catalogo) gli ordini e i preventivi. Se lo modifico per rendere la struttura corretta a livello tecnico (come mi piacerebbe molto fare) e tuttavia non funziona più, il mio lavoro ne risentirebbe e questo mi spaventa, a meno che non ci sia un modo per correggere la struttura (normalizzazione ad esempio) senza rischiare di demolire le fondamenta

 

[MajP]

I wondered why you leave the tbProductsSubCategories table ‘isolated’, when I created it (with the intention of creating a join table) it had seemed the most correct choice to hold together the many-to-many relationships I seemed to identify in my settings

I left it because was not sure where you were going with it. It may now make sense.
What you provided is useful.
I think the challenge there is a way to do this that is more "database correct". However I think it would be very hard to maintain the data. This means you may have to do more data input but it would be easier to do and keep up with.

 

[MajP]

When you have a supplier do you know what products they supply? Do you just know the category or sub category of products?

If I assign products to vendors I then know through the products some of the subcategories, categories, and activities. But I only know a little bit of information that way. They may carry hundreds of different types of products, but in my database I only have one. But before any products are entered I may want to know subcategories or at least categories they sell.
So you want to search for a potential supplier you may need to know what potentially they have versus what you have in the DB. This would mean some extra tables.
Never mind I see you track what categories a supplier makes.

 

[MajP]

There are a couple of ways to do this. Without knowing exactly what information you have at a given time this may or may not be correct.
I show a table for relating activities to subcategories, but I do not think I would do that. There are some pros and cons. I think the problem is maintaining that table gets hard and it is different for each product. If not that table has to be really precise.

For example I could have some cleats that work for Football, American Football, Rugby . I have another specific pair that only really work for American Football. So instead of establishing this relationship in a Activity_SubCategory table it is specific to a product.

When you enter a supplier into a DB I assume you record more information than just the supplier name.
But what information about that supplier do you know.
Maybe you only know the Categories that they carry
Maybe you know the sub categories
Maybe you only know the activities
Or maybe you do not care until you enter products into the database.

I provided a way to do one or all of these.
You could assign multiple categories if that is all you know
you could assign multiple subcategories and in turn you know the category
If you decide that you want to relate subactivities to categories than you also know activities through the subcategories (I am not showing that)
You could assign just activities if that is all you know
In theory that is possible, but seems like a pain to manage, and are your really going to need that. Figure out what you really need, and what is doable. If not you will have three subforms to build and choose to fill out. If you think you only know and need the categories then go with that.

Now you have a chicken and egg issue. Do you have products in the database for the supplier. If you do you can then assign Products to the supplier.
When you enter a product in the db you pick the Supplier from tbProductsSupplier. This form design gets complicated. You cannot pick the supplier until you add to that that table. But you cannot add to that table until created the product. So your form would have to check if the product and supplier are related if not allow you to pick any supplier and add to the table.

This also Depends. When you create a Product you can list all potential suppliers in tblProduct_Suppliers. This would be a subform. But depends what level you are tracking. If it is a generic product or a supplier specific product.
Are you tracking on ProductA and its potential suppliers or do you at a specific records
Product A_ From Supplier 1
Product A_ From Supplier 2

The way I did it was the latter. You identify the actual supplier in the IDSupplier_FK. However that field goes away if you are tracking at only the generic Product.

When you enter a product you can then assign the SubCategories and related Activities. Because I think this many to many between subcategories and activities is complicated to maintain in a single table I make it product specific. tblProductSubCategoriesActivities Here again you do not need to store the category because it come from the subcategory.

That is my guess how I would do it. This is more an art than a pure science and there are pros and cons for different designs. Hopefully someone else provides a different approach.
Again some of it depends on what information you have at a given time and what level of detail you really need to track. Theory is one thing and what you really are going to do and maintain is another. The fact that you can make complicated many to manys between subcategories and activities is different than can you fully populate it and make ti flexible for both products and suppliers.

 

[Mark_]

Soprattutto, ho il terrore di modificare una struttura che sembra funzionare perché ho paura di non sapere come gestire le conseguenze di questi cambiamenti. Utilizzo solo questo database, con esso gestisco i prodotti, il catalogo (infatti la richiesta della visualizzazione dell'immagine con cui ho aperto questo thread era proprio per avere una soluzione più snella per la visualizzazione delle porzioni filtrate di questo catalogo) gli ordini e i preventivi. Se lo modifico per rendere la struttura corretta a livello tecnico (come mi piacerebbe molto fare) e tuttavia non funziona più, il mio lavoro ne risentirebbe e questo mi spaventa, a meno che non ci sia un modo per correggere la struttura (normalizzazione ad esempio) senza rischiare di demolire le fondamenta

Not a question of "Will this work" or "How to handle changes", but "How much time do I need to invest and will it produce enough benefit to justify the effort".

7600 products. I am guessing at least 1 minute per product to make sure you identify each way you will want to find it. Is this benefit worth investing 3 (or more) WEEKS just matching categories to products?

If you say "YES, this is worth doing", then we can help normalize your data to make it more useful to you. If you say "No, this will not be worth the time" then keep to the more limited scope you have.

This is a cost/benefit question normally asked at the beginning of development. It may take a day or two to build the tables and change your forms, test them to make sure they work, and have your change done. The hard part comes after when you update your data.