Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    38

    Unanswered: getting the database to do the work for me nd completing fields automatically

    Apologies if this has been posted before (I'm sure it has). It is a Friday afternoon query i'm afraid!

    I have a number of tables in my database, and I am tryign to get the database to do some of the work for me.

    Table 1 - (Main table) holds 1700+ records including several that are used on more than one record (ie manager, CC, GR, CL / Courier,ad1,ad2,ad3,ad4,sloc / handset,type)

    Tabel 2 - (Courier) Contains a list of all the locations we have (over 300) with courier being unique, ad1,ad2,ad3,ad4,sloc all fields of the same record (ie a route name, with their address and location (nt all have location codes)

    Table 3 - (manager) holds 100+managers with fields GR, CL, CC

    Table 4 - (Phone) holds models of phone used and their type

    what I am trying to do, as one record in tables 2,3,4 relate to many records in table 1 (this is the main table we use and store data in). I have a Form that is completed for each record and would like to be able to type in the courier name and have the related fileds auto populate, likewise the managers name and the rest populate and phone model and fields popluate. Ideally by entery place 1 in courier and the rest filling in and saving to table 1 etc.

    I have tried look ups in the past but whilst partially successful, havent worked very well.
    I do use VBA, but am not brilliant in it (I know how to do this in Excel, but it is eluding me in access.

    Any help is appreciated.

    TIA

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Are you familiar with normalisation? For example - are you using the courier's address details in the (Main Table) as defaults that can be overwritten or should these always match the courier address details (i.e. if a courier's address changes should this change be reflected in the Main table? Similarly, are you allowed to change the address details in the main table so that they no longer match the courier?)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2007
    Posts
    38
    I have in the courier list a complete set of data, the courier address is a combo box based on this information, so although can be overwritten, hopefully will only be able to use this list. (is this normalisation???)

    MTIA

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No.

    My point is you have the courier address in Courier AND in main table. Must these be in synch (always matching for each given courier) or are they allowed to differ from each other? Forget about combo boxes etc for now - we are just theoretically speaking for now.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2007
    Posts
    38
    I follow you, YES they will be the same in the 2 tables

    Quote Originally Posted by pootle flump
    No.

    My point is you have the courier address in Courier AND in main table. Must these be in synch (always matching for each given courier) or are they allowed to differ from each other? Forget about combo boxes etc for now - we are just theoretically speaking for now.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case you should not have this problem in the first place. See the entry in my location? It is a play on a little database saying:
    One Fact in One Place.
    You have facts in several places.

    Your Main Table should not exist. You get this information by creating a query joining the other three tables to get the data you want. This way, any data changes in the three tables are automatically picked up by the query.

    Here is an excellent article on database design and normalisation:
    http://www.tonymarston.net/php-mysql...se-design.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon - I have reread your database structure. You still do have Main Table (bad name for a table BTW) but it will only contain the primary keys of the other three tables. You do not include non-primary key columns from the other tables. You will get these values (address etc) in the query mentioned above, joining Main Table to the other three tables.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2007
    Posts
    38
    Main Table is actually called something else, but used this name here as it is the main data store.

    I am building off of an existing database so have the various bits from a previous user that i am trying to put together.

    Have also been looking through a few other threads and it appears that a Dlookup will work and do what i waht it to (although in previous attempts I couldnt get it to do what i wanted, did try this Friday PM and it seems to have worked

    Many thanks

    Quote Originally Posted by pootle flump
    Beg your pardon - I have reread your database structure. You still do have Main Table (bad name for a table BTW) but it will only contain the primary keys of the other three tables. You do not include non-primary key columns from the other tables. You will get these values (address etc) in the query mentioned above, joining Main Table to the other three tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •