Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Creating a Database, Need a little Help

    Hi there,

    I am new to this forum and not very advanced with access.

    However, I wanted to create a database and was having a little trouble.

    I wanted to create a database which has a list of companies and all its retail sites.

    So for example. 1 company might have 10 different retails sites.

    This is as far as I have got but wanted to implement some additional features.


    Currently, Each store has a lookup wizard where you can select, "Signed" "Yet to Sign" or "Removed" Would it be possible to have the record move to a different table when "Removed" is selected?

    An effective way of adding, removing and editing fields without having to go into the table each time. I am thinking forms but I don’t know if it is possible.

    I would appreciate any help.

    Thanks!
    Attached Files Attached Files
    Last edited by aj_07; 10-07-11 at 09:04.

  2. #2
    Join Date
    Oct 2011
    Posts
    16
    Have a separate table called "Signed," or whatever you like. If the store's unique identifier is on this list, it has a sign. For unsigned stores, use an unmatched query.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you move a row (store) from one table to another based on the value of a column. htats smacks of flaky design to me.

    A business exists
    It has 0, 1 or more premises

    so based on that I'd want (at least) 2 tables
    one store details about the the business (eg its name, its registered office and so ont)
    one to store details of its premises (eg the stores address)
    arguably one to identify the type of premises (eg office, retail, A3 and so on), the PK of this table becomes a FK in the premise details table
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2011
    Posts
    3
    Thanks for the reply guys.


    I was only thinking, If a store becomes "Removed" it would move it away from the Company list and place it in a separate table Called "Removed Companies" for future reference. Why would this be wrong?

    I do have the set up that you are saying. I have one table for the companies and one table for the store details.

    I wanted to control all of this through a form but i dont really know how to go about it.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just flag the row as 'removed' / deleted or whatever. you could of course elect to physically delete the row is you preferred. but tis not a smart design call to store identicla data in separate tables.

    when you want to retrieve 'active' or inactive premises then put a where clause on the
    select statement. say you decide to flag premises as active or not using a column called say 'IsActive' a boolean (yes/no) datatype with a default value of true. youy where clause becomes

    WHERE IsActive = true //to retrieve rows that are active
    WHERE IsActive = false //to retrieve rows that are inactive
    omitting the IsActive column from a SELECT clause will return all rows that meet other criteria
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2011
    Posts
    3
    Quote Originally Posted by healdem View Post
    just flag the row as 'removed' / deleted or whatever. you could of course elect to physically delete the row is you preferred. but tis not a smart design call to store identicla data in separate tables.

    when you want to retrieve 'active' or inactive premises then put a where clause on the
    select statement. say you decide to flag premises as active or not using a column called say 'IsActive' a boolean (yes/no) datatype with a default value of true. youy where clause becomes

    WHERE IsActive = true //to retrieve rows that are active
    WHERE IsActive = false //to retrieve rows that are inactive
    omitting the IsActive column from a SELECT clause will return all rows that meet other criteria
    Oh ok, I get it. Instead of placing it in another table, search for them instead.

    Thanks for your help. I just wish i knew more about access. Ill see where i get to and if get stuck ill post back.

  7. #7
    Join Date
    Oct 2011
    Posts
    16
    So, each record will have an inactive flag? That sounds like bad design to me. You'd have a place holder taking up space (Granted not alot of space) for data you might never use.

    I'd have an "Inactive," table. It's foreign key would be the primary key fro your site table. If they're a member of the table, they're inactive.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in years gone by where disk space was very expensive and could take a while to retrieve then storing an extra couple of bytes per row might have been an issue.. it isn't now. If you have millions, possibly hundreds of millions of companies and or retail premises then there may be an issue.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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