Results 1 to 8 of 8

Thread: Library System

  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Library System

    I have a problem I need help with. Its a software library system

    I have partly developed a system in which each time a customer is added a new table gets created for the specific customer (via a copy of a generic table) the primary key (for these customer tables) are made up of a composite of a user defined prefix eg ABCD and a auto number. The user who creates the table has to also specify a table name. All this info gets kept in a Customer table.(tblCustomer). So the tblCustomer has a list of all the customers together with their repective prefix and table names where their info is stored.

    I have an input form (unbound) to add information to the specific customer table its done by first choosing the customer (via combo) then the respective table name appears. The Insert statement of the form (frmAddAItem) grabs the table name (via the text field puts it in a variable) and then inserts the info to the respective table. This all works well. How ever on inserting the record I need the reference number (which is an autonumber from the table) to appear on the form.

    I can't seem to do this.

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    Bloat

    One thing you may want to be concerned with is DB bloat. If you are creating a table for each and every user you are going to get an incredible amount of tables over time. If you have 5 new renters/week over a 1 month period you are going to have 1050 tables, now take that times one year (1260) then by multiple years(3 years 3780). It could get enourmous. and I don't even know if Access can facilitate that number of tables.

    Iwould create a table to store just the renterID and the items rented along with any pertinent information.

    As for storeing the Unique identifier for the user you can set the combobox to store the value of the table based on thier name Set the recordsource to Select name, referencenumber from tblcustomer
    set the bound column to 1 and the visible columns to 2. Set the column widths to 1,0 (this will hid the second column on the combobox) Then on the after update pass the value of the combobox column 1 (Ths is the index of the box and numbering starts at 0) .

    This s hould work. but like I stated earlier, you may have bigger problems down the road with this DB.

  3. #3
    Join Date
    Mar 2004
    Posts
    4

    A Library System

    Ok thanks for the help. I have another problem.

    I have a db in which each customer has its own table. There is a central customer table (tblCustomer) which contains the name of the customer the table name in which the customer details are stored and if it is to be archived or not.

    The trick is that when Iam searching the database. The users want to be able to seach the whole database for example for a software (eg Microsoft Office Istallation diskettes) could have been purchased for 3 customers CustomerA, CustomerB and CustomerC therefore tblCustomerA would have a listing, tblCustomerB's table would have a listing and so would tblCustomerC.

    How do I search all these tables when I don't know the table names. Note all the table names are contained in a table named tblCustmer. tblCustomer has a autonumber as a primary key which uniquely identifies wach customer, The Customer Name, The name of the table in which the ustomer details are stored (text field).

    How can I do a search which will go to tblCustomer get all the table names and then look in each one of those tables for records that meet a criteria

    The individual tables get created automatically when a user clicks a create table button on a form which contains a table name. The table is a copy of a generic table.

    Please help!! thanks

  4. #4
    Join Date
    Mar 2004
    Posts
    6
    Please don't take this the wrong way, but you should by a book on Access and database basics. Just the tips alone will make it money well spent.

    Creating a separate table for each customer just won't work as mjweyland pointed out and as you are now finding with the difficulties that you are running into with the database.

    You should have one table (tblCustomer) that stores all customers and their information and another table (tblPurchases) which will hold all of the purchases by all of the customers. tblPurchases will then be linked back to the tblCustomer using the CustomerID field. This will make it a lot easier to do searches and reports.

  5. #5
    Join Date
    Mar 2004
    Posts
    4

    A Library System

    I know what you mean but I Have to to a certain extent follow the requirements of the spec ad the spe wanted a separate table for eah customer. The issue is they want a Reference Numer for each Customer whih has a customer specific Prefix and an auto number.

    So please try and help Me or maybe suggest away I can have one Customer Table but generate a specific Reference for each of them.

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Okay...

    Does the customer have to enter that text to go with the auto number?

    If so I am sure there is a way to put an autonumber field together with a user entered field to make the combination you need and still only store it in one field in the one customers table.

    I will try to help if at all possible, but I am not much past a beginner with Access myself.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: A Library System

    Originally posted by Anjella Jumar

    I know what you mean but I Have to to a certain extent follow the requirements of the spec ad the spe wanted a separate table for eah customer. The issue is they want a Reference Numer for each Customer whih has a customer specific Prefix and an auto number.

    So please try and help Me or maybe suggest away I can have one Customer Table but generate a specific Reference for each of them.
    You already answered your question, and your client is requesting you place them all in the same table with a primary key set to autonumber...

    ... you're doing this as a consulting job?


    ...

  8. #8
    Join Date
    Mar 2004
    Posts
    4
    Anyway Thanks everyone I worked around it. I have tables for the client to get the ref and prefix but I put all the info in one table. re designed the Db and it works fine. I don't need to refer to the individual tables at all

    Thanks for everyones help!!!

Posting Permissions

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