Help Designing table structure 4

[dmuroff]

Good day,

Sorry for posting in 2 forums. Thought the question would be more relevant here.

I am in the process of creating a database in Access.

I need some advice on designing the table structure.

I have 10,000 products.
There are 40 product types.
Some fields are the same for all products. (I.E. description, price).
Some fields are unique to the product type.

I am thinking this:

1. Have a table called products with all 10,000 products including description, price, producttypeID and productID.

2. Have a table with producttypeID and ProducttypeDescription.

3. Have 40 tables with ProductID and fields that are unique to that category of product.

ProductID would be the PK in the products table and in the 40 other tables. I would have to have 40 relationships. 1 for between productID and the ID in the 40 tables.

Is this the right way to go about it?

Thanks in advance.

 

[dmuroff]

Hi Mark,

The only reason why I am looking to take 40 tables and put them into 1 is because querying the data will be easier this way as well as I will not have to create 40 forms (if I choose to use forms).

This SHOULD be a 1 time thing (i.e. this list of products and specs will never have to be updated).

Now let me see if I understand you correctly.

Use an append query on all 40 tables to get the like fields together.

Then after that is complete just copy and paste the remaining into the master file?

If it has to come to that I will do it but I must have something better to do with my 8 hours today than copying and pasting

Have a good day!
Dan

 

[mbuell]

Never say never, but very good -- one time effort.
Before you go and make the master table, let's think. You want the data in one table so as not to have to make a lot of forms. Hmmm. I don't think you have to make a lot of forms, just because you have a lot of tables. Lots of options, techniques. Maybe better if we have 3 tables total, but we'll see.
You could do main form/subform where subform displays in datasheet. Restrict main form by category. Your subform is going to lookup by category, even if from multiple tables. Either a little concatenation code to do a new string, or just a field to lookup, this is doable. One subform, multiple categories. Want to see all categories? Then design a view only form with that massive crosstab or intermediate table I described. Regardless of whether it is one table or multiple tables, you are probably going to have to use at least one subform, and perhaps one sub-subform.

:-0 2 or 3 table alternative: name all detail fields as detail1, detail2, detail3, etc. Create a table to hold the detail descriptions for a category. You can use this with the flat primary table, or with multiple related tables for the detail, or even with a single related detail table. The single flat or the single related detail tables mean that you cannot store your data with different characteristics (eg.: detail1 will always have to be integer, detail2 is always text, etc.). The category detail related table is probably my preferred method - but I haven't seen your data. The multiple related tables would be a different technique - and off the top of my head, the only reason for this would be to maintain different field characteristics. If all detail fields can be text or numeric, and simple, then why would we do multiple tables? Don't know.

The more I think about this, the more I like this approach: one table for products, with the like data that describes products; one table for category details, as this data describes category; and one table for category detail descriptions, as this data describes the category details.
->>--->
Gathering the data:
make the master table (w productID as key?),
import the 40 sources
design one append query with the first source,
run the append, check results,
open the append in SQL view, replace source name with source name #2. If necessary to do multiple replacements, plop the SQL into Word, do universal search and replace, plop back into query SQL view. You may find it just as fast to open the sources in Excel, copy the data and append to the table. Both methods work, with 40 tables of avg 250 records ea. should be quick, no prob. Maybe 1 hour, maybe 4 if it is really hateful type of work for you. If it takes more than 4 hours, then we haven't described the task well enough here for me to grasp the scope.

Run query #2, check results (compare record count), repeat to the end.

As for the details. Are we putting them in one flat table? or in one detail table? or in multiple tables? If in multiple tables, well - you've already got the multiple tables. This part is done. But remember, I think the best way is one detail table - so we need all data in one table along with a NEW identifier to tell us which category it belongs to. Personally, in a case like this, I'd probably dump the records into excel, manipulate them there using autofill etc to add my categoryID, etc. Then cut and paste in Excel still, when all records were in one list then I'd paste append to my detail table. This would allow me to crossreference my "detail1" "detail2" to the detail description in the same workspace. I can use the same list to paste append a column or two into the <category detail description> table. This IS a one-time effort.

If this is still more work than your boss wants you to put into it, then you may want to consider dumping the list into Excel as a flat table.

But, if we've now got 3 tables, designing forms and reports is more straightforward.




Mark
<O>
_|_

 

[dmuroff]

Thanks for all the help Mark!

I will certainly take all your suggestions into consideration while designing this DB.

Take Care,
Dan

 

[dmuroff]

But...if that's not the case, then Access will handle 255 fields, and you can write code based on what shows on the form.

For example, you would have a 3rd table called, say, tblProdTypeFields
This would have fields:
ProductType
FieldToShow
FieldTabOrder

Now for each product type, you have a record containing that typeid and the fieldname that must show for that type.

Then in the form, you loop through the table and place controls based on their existence in the table for that producttype, and the tab order telling you where to place it. You might have an area with all of the common fields, and then only have the 'variable' fields in this table. Using the .Left, .Top, etc, you can easily and neatly place these controls as you step through records on the form.

Hey Jim,

Could you elaborate on your last paragraph here? I am uncertain how the controls will be neatly placed.
Thanks!

 

[jsteph]

Ok, the dataset for tblProdtypefields might look like:

ProdType       FieldToShow    FieldTabOrder
WIDGET         LENGTH         1
WIDGET         COGS           2
WIDGET         DIAMETER       3
TRANSISTOR     OHMS           1
TRANSISTOR     FORMFACTOR     2
...ETC

So in the current event of the form, you'd check the ProdType, and open a recordset on that table based on, say, 'Widget'.
The form has every field defined, and they can be all tucked away in some corner of the form. I would hide them under an opaque rectangle so you can leave them visible, since toggling the .visible property is time consuming.

All common fields, such as ID, Description, and other fields that are common are already arranged in one area.

Now you have an area where these fields will be placed.
These fields can be pre-sized to make the code to place them easier.
The pseudocode would be:

set rst = db.openrecordset("select * from tblProdTypefields WHERE ProdType = '" & me!ProdType & "' order by fieldtaborder")
'run a loop to move back all fields from previous
for each c in me.controls
   if left(c.name,6) = "txtvar" then 'you prefix these controls uniformly
       c.left = 0 'or wherever the 'hidden' position is
       c.top = 0   'or wherever the 'hidden' position is
   end if
next c

iCtlHt = 500 'whatever value the width of the conrols are--they should be the same
'have a line or something to have as an upper boundary
iRowStart = me!linebound.Top
iColStart = 0 'start column of fields on left edge
iWidth = me.width 'how wide you've defined the form
do until rst.eof
    strfld = "txtvar" & rst!fieldtoshow
    if iColStart + me(strFld).width > iwidth then
       irowstart = irowstart + iCtlHt 'drop one row
       icolstart = 0
    else 'else place the control and increment the column
        me(strFld).left = iColstart
        icolstart = icolstart + me(strFld).width 
    end if

    rst.movenext
loop

This is just an ad-hoc pseudocode, but you should get the idea. You'd want to get more elaborate probably, but this is the general idea. There are ways to improve performance tremendously, but I didn't show that here because I wanted to keep it simple. One is field arrays, and there are others as well, but this can be done so that you can flip through records and little or no lag would be noticed.
--Jim

 

[dmuroff]

Much appreciated Jim!

Best regards,
Dan

 

[dmuroff]

Hey Jim,

Please bare with me for this one as this is all new to me and I am not a programmer.

Here's what I got so far:

Option Compare Database

Private Sub Form_Current()
Dim strFld As String
Dim C As Variant
Dim IctrlHt, iRowStart, iColStart, iWidth As Integer
Dim db As Database
Dim rst As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("Select * from ProdTypefields WHERE ProductType = " & Me!Type & " order by fieldtaborder")
    For Each C In Me.Controls
        If Left(C.Name, 3) = "txt" Then
       C.Left = 0
       C.Top = 0
    End If
Next C

iCtlHt = 500
iRowStart = 1
iColStart = 0
iWidth = Me.Width

Do Until rst.EOF
    strFld = "txt" & rst!fieldstoshow
    If iColStart + Me(strFld).Width > iWidth Then
       iRowStart = iRowStart + iCtlHt
       iColStart = 0
    Else
        Me(strFld).Left = iColStart
        iColStart = iColStart + Me(strFld).Width
    End If
    rst.MoveNext
Loop
End Sub

All the data is being displayed on 1 row. It is not switching rows although it is going to the beginning of the row when it is greater than the width of the form.

Also, how do I get the labels to move with the text boxes?

Thanks!

 

[PHV]

Replace this:
If iColStart + Me(strFld).Width > iWidth Then
iRowStart = iRowStart + iCtlHt
iColStart = 0
Else
Me(strFld).Left = iColStart
iColStart = iColStart + Me(strFld).Width
End If
By this:
If iColStart + Me(strFld).Width > iWidth Then
iRowStart = iRowStart + iCtlHt
iColStart = 0
End If
Me(strFld).Top = iRowStart
Me(strFld).Left = iColStart
iColStart = iColStart + Me(strFld).Width

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dmuroff]

Also, how do I get the labels to move with the text boxes?

 

[jsteph]

Dmuroff,
You want to name your labels in a similar fashion, ie, you prefixed the textboxes with "txt" (which, I would recommend changing to something more unique--since 'txt' is what I use to prefix all my textboxes, and these belong to a unique group--the moveable ones, so maybe 'txtmov' or something like that)

...anyway, so prefix the lables, say 'lblmov', then the name in the 'fieldtoshow' field in the prodtypefields table.
You'll need to do the math as to where you want to place the lables, but since the text in the lables will vary, you might want to put the labels above, rather than to the left of, the textboxes. It's all doable, it's just a matter of some tedium to start with, then once you get it working, it's easy to change things or add/remove fields.
--Jim

 

[dmuroff]

Got It!
Here is my final code for all those interested:

Option Compare Database
Option Explicit

Private Sub Form_Current()
Dim strFld, strlblFld As String
Dim C As Variant
Dim ictlHt, iRowStart, iColStart, iWidth, Count As Integer
Dim db As Database
Dim rst As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("Select * from ProdTypefields WHERE ProductType = " & Me!Type & " order by fieldtaborder")
   For Each C In Me.Controls
       If Left(C.Name, 6) = "txtmov" Then
      C.Left = 5000
      C.Top = 5000
      End If
      If Left(C.Name, 6) = "lblmov" Then
      C.Left = 5000
      C.Top = 5000
   End If

Next C
Count = 0
ictlHt = 300
iRowStart = 1950
iColStart = 95
iWidth = Me.Width

Do Until rst.EOF
    strFld = "txtmov" & rst!fieldstoshow
    strlblFld = "lblmov" & rst!fieldstoshow
       If Count = 1 Then
       iRowStart = iRowStart + ictlHt
       iColStart = 95
       Count = 0
    End If
    Me(strlblFld).Top = iRowStart
    Me(strlblFld).Left = iColStart
    Me(strFld).Top = iRowStart
    Me(strFld).Left = iColStart + 1400
    Count = Count + 1
    rst.MoveNext
Loop
End Sub

Thanks for your help guys! Much appreciated!

Dan