Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Building a table (Access 2000)

    Edited by HansV to add <!t>[pre] and <!t>[/pre] tags around data, otherwise the question is incomprehensible

    I have to build an expression for the outlays.They are divided into fixed and added.They are in fact 2 columns for example
    <pre> lrs dlrs
    fixed added
    rent 200 10
    water 35 20
    </pre>

    Before building the table could you advise me shall i build 2 tables, called TblFixedoutlays and tblAddedOutlays with exactly the same structure, or shall i build only one table with 2 fields for each outlay ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Building a table (Access 2000)

    Don't create two tables with identical structure.

    If there will only be two kinds of outlay, you can use one table with two fields (fixed and added).

    If there is a chance that there will be more kinds of outlay, create a table like this:

    <table border=1><td align=center>Item</td><td align=center>OutlayType</td><td align=center>Amount</td><td>Rent</td><td align=right>1</td><td align=right>200</td><td>Rent</td><td align=right>2</td><td align=right>10</td><td>Water</td><td align=right>1</td><td align=right>35</td><td>Water</td><td align=right>2</td><td align=right>25</td></table>
    and a separate table for the outlay types:

    <table border=1><td align=center>OutlayType</td><td align=center>Outlay</td><td align=right>1</td><td>Fixed</td><td align=right>2</td><td>Added</td></table>
    This will make future modifications much easier.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building a table (Access 2000)

    thank you very much hans. it is much better to have the right direction before starting the ball rolling.i will follow your advice

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building a table (Access 2000)

    how can i relate the two tables so that to obtain the type from the basic table ?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Building a table (Access 2000)

    I'd do it differently. See the attached database.
    Look at the design of the tables, and also look at Tools | Relationships.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building a table (Access 2000)

    Thank you Hans ! You are so clever

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Building a table (Access 2000)

    In your wonerful construction i wanted to replace the table tblItems with the existing table affiliates.How could i replace it,shall i use the afid numner and shall i use the lookup wizard ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Building a table (Access 2000)

    1) Select View | Relationships.
    Select the line joining tblItems to tblData, then press Delete to remove this relationship.
    Click on tblItems, then press Delete to remove the table from the window.
    Close the Relationships window.

    2) Open the table tblData in design view.
    Change the name of the ItemID field to afid.
    While afid is the current field, activate the Lookup tab.
    Change the Row Source to

    SELECT afid, CompanyName FROM affiliates

    Close and save tblData.

    3) Open the Relationships window again.
    Select Relationships | Show table...
    Add the affiliates table, then close the 'Add table' dialog.
    Drag the affiliates table to a convenient place in the Relationships window (not overlapping the other tables).
    Create a relationship between affiliates and tblData by dragging the afid field from one of them to the afid field in the other.
    In the dialog that appears, check the 'Enforce referential integrity' check box and also the 'Cascade delete related records', then click OK.
    Close and save the Relationships window.

    4) Delete the tblItems table from the database window.

    You have now completely replaced tblItems with affiliates.

Posting Permissions

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