Results 1 to 7 of 7
  1. #1
    delta is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    19

    To create a database

    I want to create a data base for my filing. need 2 tables.
    Table 1 : id no (primery key), file name
    Table 2 : id no (foreign key) and auto number, task name

    id no. in table 1 to table 2 should be one to many. In table one each record I want auto number.


    For this purpose what is data type of table 1 id no. field and what is data type of table 2 id no.

    Please comment.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I use this method all the time.
    tClient tbl
    ClientID (auto)
    Name, addr, etc

    tDocuments tbl
    ClientID (long)
    DocID (auto)
    DocName

    the master table ID is autonum
    but in the child table, tDocuments, ClientID is long integer (same as an autonum but not auto)
    tDocs has its own autonum

  3. #3
    baderms is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    8
    Table 1: Tbl1ID as autonumber Table 2: Tbl2ID as autonumber
    Fld2 as string FKID as long
    Fld3 as integer Fld1 as string

    The join Tbl1ID to FKID

  4. #4
    delta is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    19
    Please explain about why we give auto number in tClient tbl
    and why ClientID is long for tDocuments tbl

    Once I give auto number in tClient tbl I will get auto numbner. yes?
    I need auto number only in tDocuments tbl.
    Please comment.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    delta,

    Please see some of the links in this post for database planning and design concepts.
    Work through 1 or 2 of the tutorials mentioned --you will learn if you work through them.
    Good luck

  6. #6
    delta is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    19
    Dear ranman256,

    Let me explain another way. I have a box file to file my daily activities.
    For example I have 10 staff. Staff number 1, DocID starting from 101.
    For staff number 1 I have forwarded a paper to HR to renew residence card.
    For this I need DocID 101. Then 3rd Staff told me he need to renew his
    passport. Once I enter in DocID(auto) I will get auto number 102.
    Then staff eight came to me. He has other process Automatically
    msaccess will give 103.
    All this I will file in a box file. So it will be as follows :
    Staff 1 = 101
    Staff 3 = 102
    Staff 8 = 103, 105
    Staff 5 = 104
    After I get 104 I will access Staff 8. Then auto number will give 105 in Staff 8.
    In case I file all these number physically in a box file I can find out number serially for corresponding staff.
    In case I make ten physical file for ten staff I go to staff 8 for example. I can find two items (103,105)
    This is my favorite filing system. So could you please tell me how to connect with two table.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Then let's port your example into Access.

    You have table tblStaff with fields StaffID (autonumber), ForeName (text), LastName (text);
    You have table tblDoc with fields DocID (autonumber), StaffID (long integer), DocNumber (text), ...

    Now tables are for storing the info - it is best not to give users any access to them. I.e. usually are all tables hidden from users so thei can't corrupt them accitantally. Users use forms to interact with tables.

    So you create a form fStaff (a single form with table tblStaff as source). The form has controls txtStaffID, txtForeName, txtLastName, and an unbound combo box cbbSelectStaff to select a person.
    The control txtStaffID is hidden - there is no need for user to see it, least to edit it. As it is autonumeric, it will be created automatically whenever you enter a new record. If you create a new record, but after that delete it, there will be gap in autonumbers, as access counts the deleted autonumber as used (there is a way to reset autonumbering, but let us drop it for now).

    In form fStaff the user sees the information for one person. When next row is selected, the info for next person is displayed.

    As next step, you create a form fDocs (continuous or single, depending the info needed to be displayed) based on table tblDocs, with controls txtDocID, txtStaffID, txtDocNumber, ... Fields txtDocID and txtStaffID you hide also - again there is no need for user to know about them.
    Now you insert the form fDocs into form fStaff as subform (with fStaff opened in editing mode, you drew the form fDocs into it from objects pane. Probably the forms will be linked automatically, but when not, then you can edit links manually in subforms (NB! Subforms, not Forms) properties.

    When you now open the form fStaff again, the subform will be visible too. When there are some documents for active user registered, they will be displayed in subform on separate rows (continuous subform), or you see one document info and you select next record to view next document info (single subform). When you create a new document in subform, an autonumber is generated automatically for txtDocID, and the value of StaffID active in parent form is given to txtStaffID in subform.

    When you want to keep some document numbering, for this you use the field txtDocNumber (to avoid double entries, set an unique index for field DocNumber). Of course you can also ditch DocID and use DocNumber as primary key - but don't set the field DocNumber as autonumber anyway.

    So let's see how will it look for user and what happens in tables:

    User opens the form fStaff and starts with registering staff.
    Creates new record and Enters 'John' into field txtForename and 'Smith' into field txtLastName;
    Creates new record and Enters 'Mary' into field txtForename and 'White' into field txtLastName;
    Creates new record and Enters 'Max' into field txtForename and 'Fox' into field txtLastName;
    In table tblStaff will be rows
    1, 'John', 'Smith'
    2, 'Mary', 'White'
    3, 'Max', 'Fox'
    ...

    Now the user will register documents.
    Selects person 'John Smith' from combo or simply uses Next/Previous buttons to navigate to right record;
    Creates new record and enters 101 into field txtDocNumber (, and fills other fields);
    Selects person 'Max Fox' from combo or simply uses Next/Previous buttons to navigate to right record;
    Creates new record and enters 102 into field txtDocNumber (, and fills other fields);
    Selects person 'Mary White' from combo or simply uses Next/Previous buttons to navigate to right record;
    Creates new record and enters 103 into field txtDocNumber (, and fills other fields);
    Creates new record and enters 105 into field txtDocNumber (, and fills other fields).
    In table tblDocs will be rows:
    1, 1, 101, ...;
    2, 3, 102, ...;
    3, 2, 103, ...;
    4, 2, 105, ...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-20-2016, 07:06 PM
  2. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  3. Replies: 14
    Last Post: 12-28-2015, 07:51 AM
  4. How to create a database inside database?
    By rajendraladda in forum Access
    Replies: 1
    Last Post: 10-12-2014, 12:42 PM
  5. Replies: 4
    Last Post: 10-31-2013, 12:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums