Need help with database design and implementation.

[archaeofreak]

My boss wants me to create a database in Access 2007 that can do several things, but I am an Access beginner (but the most experienced in my small office).



The first form the user sees when opening the data base would be a master menu of checkbox choices. The user selects any combo of choices, and then a second form should auto-generate, and shows a variety of other buttons, comboboxes and textboxes for recording information associated with only the choices that were selected in the master menu via the checkboxes.

In the autogenerated form, I need to be able to enter basic count and attribute information for a collection of objects in an initial cataloging. Data would be entered via the form, linked to a master table. Standardized choices on the forms combo-boxes come from associated tables.

I then need to be able to link this initial inventory to a second table/form, with additional attribute information recorded via a combination of text, check and combo-boxes in a "detailed analysis".

However, only a sample of the objects from the initial cataloging would later go through the "detailed analysis" process.

I am thinking a work flow as described below:

User opens database for first time. A Main Menu opens with three button choices; "Variable Selection", "Initial Inventory", and "Detailed Analysis". Only "Variable Selection" is click-able (other 2 are greyed out for now).

User selects "Variable Selection" in Main Menu and a second menu form pops up with check boxes for variables to be selected.

User checks off the variables they want then clicks a "Generate" button. Nothing apparent happens other than a new button appears next to the "Generate" button called "Return".

However, not yet visible to the user, another form is autogenerated from the prior "Generate" button click, with all of the necessary text, check and combo-boxes for initial inventory (this would be the same no matter what variable are chosen in the previous checkbox variable selection menu, because initial inventory is always the same for each collection of objects-but the variable choices chosen do matter for the "Detailed Analysis" form to come).

When the user clicks "Return" button, they are taken back to the original menu with "Variable Selection", "Initial Inventory", and "Detailed Analysis", however now "Initial Inventory", and "Detailed Analysis" are click-able, with Variable Selection greyed out (since it has been used, although I need a way to be able to re-select attributed if one is forgotten).

User clicks "Initial Inventory", and is taken to the "Initial Inventory" form where they can begin inventory of the collection of objects.

Once collection of objects has been inventoried, a button called "Initial Inventory Complete" at the bottom of the Initial Inventory" form is clicked. This closes and completes the data entry for the "Initial Inventory" and returns the user to the Main Menu.

Now, in the Main Menu, "Variable Selection", and "Initial Inventory" are greyed out, and only "detailed analysis" is clickable.

User clicks "Detailed Analysis" and another form pops-up. From this form, called "Sample", the user can select a sample of the objects recorded in the "Initial Inventory" table/form based on provenience (origin of object). These data are recorded in another linked table/form separately. These are the objects that will undergo detailed analysis.

The contents of this form depend on the initial selections when "Variable Selection" was clicked (eg. the combination of checkboxes selected for various variables).

Once all the objects to undergo detailed analysis are sampled, the user clicks a button called "Begin Analysis" at the bottom of the "Sample" form.

All of the records that were selected from the "Initial Sort" table/form from choices in the the "Sample" form are selected by a query from the "Initial Sort" table; this query creates a new table called "Detailed Analysis", and appends into it the copied data.

The data is then displayed in the "detailed analysis" form with the additional variables for recording displayed.

I know this is convoluted, but its hard for me to describe all of the things I want to happen in English.

If anyone can help me with this data base design, I would appreciate it, and maybe even donate $50 bucks into a paypal account :-)

When the user is in the detailed analysis form recording more info on the sample of objects inventoried in the initial inventory, additional choices are "unlocked" or made visible based on the users selection of various combo box choices. I think I have this figured out in a test with some simple VBscript.

Select Case Me.Unit
Case "Vessel"
Me.[Estimated Vessel Completeness].Visible = True
Me.[Reconstructed].Visible = True
Me.[Number of vessel sherds].Visible = True

Case Else
Me.[Estimated Vessel Completeness].Visible = False
Me.[Reconstructed].Visible = False
Me.[Number of vessel sherds].Visible = False
End Select


I have no idea how to auto-generate the form and tables ready for data entry, from a menu of checkbox choices. I would also want the master menu to be invisible after the user autogenerates the first form/table for initial inventory of a collection from the check boxes (eg. it should only be used once, on opening the db. The empty db design would be copied for individual projects.)

And, I am confused as to how to link these tables and forms, so when entering or browsing through previously entered records, they are both linked and the user can switch seamless back and forth through initial inventory data and detailed data on the sample of objects within the initial inventory.

One of the issues I am having is that objects recorded in the initial inventory can be grouped together (say 5 like-objects are recorded in a single record), but in the detailed analysis form each of the 5 objects recorded in the initial inventory would have different information recorded for them individually-in separate recorded. How to keep track of that?

Thanks and if there are any questions, ask!

[June7]

You 'want' to happen - you haven't built anything?

First step is to identify data entities and relationships. Get data structure correct before even thinking about forms and reports and how you want them to behave.

I am sure everything (at least most) you describe is possible but not going to try addressing all here. When you encounter specific issue during development, post question and hopefully someone will be able to help.

Does sound like you need to explore form/subform design for the inventory and inventory detail related data.

The last issue sounds like multi-value field. I NEVER use multi-value field.

[archaeofreak]

You 'want' to happen - you haven't built anything?

First step is to identify data entities and relationships. Get data structure correct before even thinking about forms and reports and how you want them to behave.

I am sure everything (at least most) you describe is possible but not going to try addressing all here. When you encounter specific issue during development, post question and hopefully someone will be able to help.

Does sound like you need to explore form/subform design for the inventory and inventory detail related data.

The last issue sounds like multi-value field. I NEVER use multi-value field.

Thanks for replying.

I have a very basic design going right now.

I have a printed doc with all of the variables.

I am trying to begin to create the database but I understand if its too complex for someone to help with.

Thanks again for trying!

[ssanfu]

It is not that it might be too complex (there are a LOT of great minds here), but what questions do you have???

If you ask "How does this design look? Any suggestions?", you will get responses. If you ask "How do I link he tables?", you get responses.
If you say

I have a very basic design going right now.

I have a printed doc with all of the variables.

I am trying to begin to create the database but I understand if its too complex for someone to help with.

there is nothing to answer.

The point is YOU have to build the dB. Ask all the questions you want.
Provide the table designs, field names, ... ; the more info the easier it is to get an answer.
So far you have not provided much....


When I start designing a new database, I use a dry marker and a whiteboard/window. The progress to pencil and paper. Last is creating the tables.
Add a few dummy records to see if the design is workable. then on to all the other stuff.

So ask away!!

And Welcome to the forum

[orange]

I recommend you work through this tutorial. You will learn about design and tables and relationships.
http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck.

[archaeofreak]

I recommend you work through this tutorial. You will learn about design and tables and relationships.
http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck.

How about this:

I have a form with 2 checkboxes.

How do I use VBA to check to see if either of the checkboxes are checked- then if one (or both) are checked, create a table with the checkbox label as a column in the table?

[June7]

Why do you need to create a table programmatically? This indicates possible poor design.

Where would the table name come from?

This would require a CREATE TABLE sql action. Often the query design tools can help with constructing SQL statements. Then take the SQL to VBA and modify as needed.

Here is one source for learning more about SQL http://w3schools.com/sql/default.asp

The VBA would be like:
CurrentDb.Execute "CREATE TABLE " & Me.tablenametextbox & " " & Me.checklabel.Caption

[archaeofreak]

Why do you need to create a table programmatically? This indicates possible poor design.

Where would the table name come from?

This would require a CREATE TABLE sql action. Often the query design tools can help with constructing SQL statements. Then take the SQL to VBA and modify as needed.

Here is one source for learning more about SQL http://w3schools.com/sql/default.asp

The VBA would be like:
CurrentDb.Execute "CREATE TABLE " & Me.tablenametextbox & " " & Me.checklabel.Caption

I want the table name to come from what the user types into a text box.

See code below: I know its wrong with the multiple elseif's but dont know how to loop. This code creates a new table from 1 or 2 checkboxes being checked, with column names from the checkbox labels.

Private Sub btnGenerate_Click()

Dim MySQL As String
Dim exTable As String


If Me.chkvesselshape = True And Me.chkneckheight = True Then
MsgBox "Creating Detailed Analysis form with neck height and vessel shape variables!"
MySQL = "CREATE TABLE exTable (ID counter, VesselShape text, NeckHeight integer)"
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True

ElseIf Me.chkneckheight = True Then
MsgBox "Creating Detailed Analysis form with Neck height variable!"
MySQL = "CREATE TABLE exTable (ID counter, NeckHeight text (50))"
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
ElseIf Me.chkvesselshape = True Then
MsgBox "Creating Detailed Analysis form with Vessel Shape variable!"

MySQL = "CREATE TABLE exTable (ID counter, VesselShape text (50))"
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
End If
End Sub

But, what I want is for the user to enter the name of the project, and for that to be the name of the table, not "exTable"-I tried using Me.txtProjectName = exTable and Me.txtProjectName.text = exTable and several other combinations but cant get it to work.

[orange]

I don't understand why you would want the user to provide a table name. It still seems to me that you have an issue with some database concepts or
you are having difficulty communicating what your real needs are. Perhaps you could tell us in plain English what the business to be supported by the database is; what tables/entities are involved etc.

[archaeofreak]

The business is a museum collection of objects...if a new collection comes in, it needs to be initially inventoried-then, a sample of the objects is chosen for a more detailed analysis. I need to have forms and tables for the user to enter basic data for the entire collection (like counts and object type), then a separate form with multiple checkboxes would enable the user to choose from a suite of detailed variables to collect on the objects. These would then be created in a new table as fields, with a name chosen by the user (the name of the project or collection).

A form would then be autogenerated with those detailed analysis variables chosen from the checkbox form.

The user would then choose a sample of the initial inventory data based on some criteria (like object origination, type, color, etc) via a query then all of those records would be sent to a second temp table.

The query results in this temp table would be appended to the detailed analysis table, and then the user could begin collection detailed analysis variables on that sample of objects from the initial inventory table.

The initial inventory would have to be accessible, linked but seperate from the detailed table.

Hope this makes sense.

Regarding the generation of a table from checkbox choices....

This works too...

Private Sub btnGenerate_Click()
If Me.chkvesselshape = True And Me.chkneckheight = True Then
MsgBox "Creating Detailed Analysis form with neck height and vessel shape variables!"
CurrentDb.Execute "CREATE TABLE " & Me.txtProjectName & "(ID counter, VesselShape text, NeckHeight integer) "
End If
End Sub

But only because I am looking at just 2 checkboxes here. What if I have 50 checkboxes on a form, and I need to create my table from any combination of them being checked?
How do I loop through all the checkboxes on my form to see which are checked, then add those to my new table?

[June7]

This does not describe the data structure. Having to create a new table just because a new artifact is received is definitely bad design. Because the design edits would not stop with creating the table. Would have to create/modify queries, forms, reports, code. A real management nightmare and results in database bloat.

[archaeofreak]

Not a new table every time an object is received, rather each project would be within a table and each project consists of thousands of objects.

[June7]

That makes no sense to me. No idea what you mean. Related thread: https://www.accessforums.net/program...tml#post189530