Re: Database set up help

Wow.........this is great.......I did not think anyone would reply or
try to help this database out. I really appreciate you taking the time
to read my post.

Okay, let's see...I choose the y/n data type because I am using
InfoPath to create my form. I like the way it has the ability to hide
sections and produce them when someone selects a choice. So this form
will work like this:

1. User opens up form and enters Employee Information in the fields
from tblEmployee.
2. The next thing they will see is the Main Menu from the fields in
tblMainMenuOptions. This is not a dropdown menu it is a checkbox menu
because users can check off as many options from the Main Menu that
they need.

For example: 2 of the 38 options in my main menu are BPCS Applications
and Logistics Pro. (These 2 options are how I came about my
tblApplications and tblLogPro.) If a user selects both these options
off the Main Menu then the form will unhide the Applications section
and the LogPro Department section giving the users the ability to
choose department(s) and Applications.

3. Once those menus open up and the user selects a department or
departments from the LogPro section that section is complete. However,
when the user starts making selections off the Application section the
individual application sections start to open giving the user the
ability once again to choose more options off each of the Application
sections.

For Example: I have 19 Applications on my Application section. Each
Application is a 3 letter code: ACP = Accounts Payable, ACR = Accounts
Receiveable, BIL = Billing and so on...... If a user puts a check next
to ACP and BIL then those hidden sections will appear on the form with
all the options they consist of.

4. Once the Applications menus open and the user sees them, the user
can start to check off application codes on those menus.

For Example: if ACP Application Codes opens the user will see my
example from above:

ACP Application Codes ECM US Canada
(y/n = a checkbox)

ACP100 y/n y/n y/n
ACP200 y/n y/n y/n
ACP300 y/n y/n y/n
ACP400 y/n y/n y/n
and so on..............

Each user is allowed to select ACP100 ECM, US, or Canada if they need.
Or they may just need to check ACP US and none of the others. So yes, a
user is allowed to select from more than one application code.

I set up a database with this so far:

tblSecurityRequestIDs
- this table holds all my primary keys for each table I created
below
tblEmployee
- this table holds all the employee information such as name,
title, pay number, start date, requested by and so one
tblRegions
- this table has 5 fields
- REGID *
- SRID (foreign key to tblSecurityRequestIDs)
-ECM
- US
- Canada
tblMainMenuOptions
- this table consists of 40 fields (one for each option on the Main
Menu, MMOID* and SRID)
tblApplications
- this table consists of 21 fields (one for each application, APPID*,
and SRID)
tblLogProDept
- this table consists of 21 fields (one for each department, LPID*,
and SRID)

* = Primary Keys

Then I have 19 other tables which are all named tbl and their app code:
tblACP, tblACR, tblBIL, tblBOM, tblCAP, tblCST, tblDRP, tblFOR, tblINV,
tblJIT, tblMDM, tblMFS, tblMPS, tblMRP, tblORD, tblPUR, tblSAL, tblSFC,
and tblZZZ

Each of these tables has a primary key of its application code name and
the word ID: ACPID, ACRIS, BILID, BOMID, and so on and a field for each
of its selections.

whew..................yeah I know lots of tables.

I have one to many relationships going from my main table:
tblSecurityRequestIDs to all my other tables. Is this right so far b/c
I feel I may be onto something here?

But........my next question is if I have a separate tblRegion how will
I be able to produce a form like the one above in which a user puts a
check in ACP100 - US and the table shows under ACP100 that it is region
US?

Just to make you happy ......of the tables I have set up only Main Menu
Options and Regions are of yes/no data types. Each other table I kept
as text data type. Is this correct?

Well I have babbled about my database enough. Please let me know if I
am onto the right track.

Thanks,
Justine

.

Relevant Pages

  • Re: Triggers for access row
    ... I have an application which opens the database with just one login ... Even I create extra columns in the tblUser table, ... more than 20 applications using this approach... ...
    (microsoft.public.sqlserver.programming)
  • Hyperlinks
    ... On my main form of a database I have several command ... that hyperlink to applications. ... The ones that hyperlink to applications pop ... application opens. ...
    (microsoft.public.access.formscoding)
  • Re: polymorphism (was: Poly Couples)
    ... but this is not really "business software"... ... Most of such applications are built as a combination of ... database with flat files or a different RDBMS vendor?" ... couldn't care less if I do it in using structured programming or OOP ...
    (comp.object)
  • Re: Tracking Log In and Log Out times of Users
    ... The fact that the database is on the server should be irrelevant. ... Type mismatch and then highlights Set rst = ... On my startup form, (that opens every time the database is open, ... I then went to the load ...
    (microsoft.public.access.modulesdaovba)
  • RE: Compacting MDB help
    ... The autoexec macro opens a ... The FrontEnd.mdb's main switchboard has a cbo to switch back end ... FECompact.mdb runs the few lines of code to compact the FrontEnd.mdb ... I'm quite certain that you can't Compact an open database from within itself ...
    (microsoft.public.access.modulesdaovba)