Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Question Unanswered: Kind of Beginner

    I need some guidance and would like some advice from the experts. I've dabbled here and there in Access but have now been asked to pull together a database. It sounds quite complex, but really is quite simple, but I can't get my head around it!

    Picture this, a database that you have client information, supplier information, sales & purchase ledgers and job tracking. Essentially, alot of the information contained in these tables would be duplicated (e.g. Client Information, would again appear in the Job Tracking table). Also, when entering information into the Sales Ledger, ideally I don't want to have to re-enter the information, but instead for it to automatically fill out my ExCel Invoice which I've made to print off.

    Are there templates/downloads I can use and cheat, or is this a case of some extreme programming!

    Help! One very confused and very lost puppy.... right before christmas an'all!

  2. #2
    Join Date
    Nov 2003
    Posts
    34
    The whole point of relationships in tables are so you don't have repeating information. Start by designing all your screens...(right off the bat I can see a screen for entering new/deleting/editing customer information, another one for supplier information, etc.) Then once you have that, you can start creating your tables....Let me know if I can help.
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  3. #3
    Join Date
    Dec 2003
    Posts
    5
    I have created the following tables/forms:

    Client Information
    Supplier Information
    Jobs/Events
    Purchase Ledger
    Sales Ledger

    At the moment, the form is a direct replica of the table fields. What I'd like to be able to do is when I go to Jobs/Events Form and start entering a new job, I can either retrieve an existing client from "client information" or enter a "new client". The Purchase Ledger form would ideally take most of its information from the Supplier Information, but would populate the Purchase Ledger table. Then the Sales Ledger form would populate the Sales Ledger table, but then also populate the relevant cells on my ExCel Invoice to print out. Can this be done ... simply?

  4. #4
    Join Date
    Nov 2003
    Posts
    34
    If you don't mind...post your tables with all the field names. And any relationships you have set up so far. Yes, what you want can be done.

    Thanks,
    Kelly
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  5. #5
    Join Date
    Dec 2003
    Posts
    5
    how do I do that? Sorry to be a complete idiot!

  6. #6
    Join Date
    Nov 2003
    Posts
    34
    Just type in each table name and the fields you have under each table into a reply. Manually is the easiest way unless you have tons of fields.
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  7. #7
    Join Date
    Dec 2003
    Posts
    5
    OKEY DOKEY, HE WE GO!

    *Field which is ID/Primary Key

    TABLE/FORM 1 : Jobs
    Date of Event
    Event Title
    Event Venue
    Client Details
    Event Reference*

    TABLE/FORM 2 : Client Information
    First Name
    Surname
    Company Name
    Address 1
    Address 2
    Address 3
    Address 4
    Address 5
    Telephone Number
    Other Information
    Client Reference*

    TABLE/FORM 3 : Sales Ledger
    Invoice Date
    Event Title
    Invoice Number
    Line Items & Costs (x 10)
    Net Cost
    VAT
    Total Cost
    Client First Name
    Client Surname
    Client Address......
    Ledger Reference*

    TABLE/FORM 4 : Purchase Ledger
    Invoice Date
    Supplier Company Name
    Net Cost
    VAT
    Total Cost
    Event Title
    Invoice Reference*

    TABLE/FORM 5 : Supplier Information
    First Name
    Surname
    Company Name
    Address 1
    Address 2
    Address 3
    Address 4
    Address 5
    Telephone Number
    Other Information
    Supplier Reference*

    Have Fun! I know I am! Thanks a bundle....

  8. #8
    Join Date
    Nov 2003
    Posts
    34
    This is a start for how I would structure the tables but they cannot be completed until some of the following questions are answered...the questions will also determine the relationships of one table to the next so when you start to write queries they will pull properly.

    A job can have many or just one client?
    A client can be involved in many or just one job?

    Job Table
    EventID
    EventDate
    Title
    Venue

    Client Table
    ClientID
    FName
    LName
    SurName
    CompanyName
    Addr1
    Addr2
    Addr3
    City
    State
    Zip
    Phone


    Can a sale have more than one invoice?
    Does an invoice belong to more than one sale?
    Can a sale have more than one event?
    Does an event have more than one sale?
    Can a sale have more than one client?
    Does a client have more than one sale?
    Can a line item belong to more than one sale?

    Sales Table
    SalesID
    ItemID

    Item Table
    ItemID
    ItemName
    ItemDescription

    Can a ledger have more than one supplier?
    Can a supplier have more than one ledger?
    Can a ledger belong to more than one event?
    Can an event have more than one ledger?

    LedgerInformation Table
    LedgerID
    InvoiceDate
    NetCost
    VAT
    TotalCost

    Supplier Table
    SupplierID
    FName
    LName
    SurName
    CompanyName
    Addr1
    addr2
    addr3
    CIty
    State
    Zip
    Phone
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  9. #9
    Join Date
    Dec 2003
    Posts
    5
    A Job/Event will only ever have ONE client
    A Client, however, will have a number of jobs/events

    A Job/Event will only ever generate one sales ledger (Sales Invoice)
    A Job/Event will generate more than one purchase ledger (Incoming Invoices)
    One Entry into the Sales Ledger would generate only ONE invoice
    A Sales Invoice would belong to ONE Sales Ledger Entry
    ONE Sale to ONE Event

    ONE Entry on the Purchase Ledger would have ONE supplier
    A Supplier would have many Purchase ledger entries
    A Purchase Ledger would only ever belong to one event

    Blimey, that got confusing....

  10. #10
    Join Date
    Nov 2003
    Posts
    34
    I don't think you named your relationships the same way that I did so I'm guessing here. When you are doing relationships remember to think in terms of fields in the DB not fields on the screen. I don't think this is 100% right but hopefully it will get you started. Next define your relationships in tools->relationships and then start building your forms

    This is what I came up with...

    Job/Event Table:
    EventID
    Date
    Title
    Venue
    ClientID
    SalesID

    Client Table:
    ClientID
    Name (etc.)

    Sale Table:
    SalesID

    Item Table:
    ItemID
    Description
    SalesID
    SupplierID

    Ledger Table:
    LedgerID
    InvoiceDate
    NetCost
    VAT
    TotalCost
    EventID

    Supplier Table:
    SupplierID
    Name (etc.)
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

Posting Permissions

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