Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking entries in a report (Access 2000)

    I have a linked table, tblCustomers. For reporting purposes, I need to link a few customers together. (Where customers are linked, one customer acts as 'parent'). Eg In the table below, cust a, cust b and cust c are linked, with 'a' acting as 'parent

    Custid CustName Sales
    1 cust a 10
    2 cust b 20
    3 cust c 30
    4 cust d 40
    5 cust e 50

    I would want a report to show :-

    custName Sales
    a 60 (ie 10+20+30)
    d 40
    e 50
    Total 150

    Can anyone advise a simple way to set up a tables/queries to generate the report

    Thanks
    Rob

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

    Re: Linking entries in a report (Access 2000)

    See the attached demo. I added a "ParentID" field to the customers table that is set to 1 for customers 2 and 3. It is left blank for customer 1 (and for 4 and 5 too).
    I created a query to sum the "indirect" sales, and a second query based on the table and the first one to combine the sales.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking entries in a report (Access 2000)

    Thanks Hans, but this solution adds a field to the Customers table. I can't do that because it is a linked table (it updates daily from a csv file).

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

    Re: Linking entries in a report (Access 2000)

    How then are you going to specify which customers belong together? Are the customer ID's fixed? If so, you can create a separate table in your database with only CustomerID and ParentID fields to specify this relationship. See modified version.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking entries in a report (Access 2000)

    Thanks Hans

    The qryTotals is exactly as I need.

    The CustID's are fixed but the links can change.

    Customers can join or leave the parent/child relationship and so I need to be able to maintain the links table. (Customers earn higher commissions when they act as parent)

    So, the only problem I now have is being able to edit the Links table. For example, I cannot add parentID = 1, CustID = 4 to the tbl links, if that customer joined the relationship.

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

    Re: Linking entries in a report (Access 2000)

    I see that I forgot to change the Data Type of the CustID field in tblLinks (I created the table by copying the customer table and modifying the design). It should be a Number (Long Integer) field instead of Autonumber. You will have to delete the relationship on CustID temporarily in order to change the field type. Sorry about that!

    When you have changed CustID to Number, you should be able to add and edit links.

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking entries in a report (Access 2000)

    Thanks once again Hans - that's exactly as I needed

    Rob

  8. #8
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking entries in a report (Access 2000)

    I tried to incorporate the above ideas into my database. However, my customers table is a linked table and I cannot change the CustID to make it a primary key.

    I tried a Maketable query, but when I re-run the query, the existing table is deleted before being replaced and so I lose the primary key again.

    Does anyone know a workaround for this?

    Thanks

    Rob

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

    Re: Linking entries in a report (Access 2000)

    Why do you need CustID to be a primary key?

    After running the make table query once, you could thereafter run a delete query to remove existing records and an append query to add the new ones.

  10. #10
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking entries in a report (Access 2000)

    In your example tblCustomers was a one to many relationship with tblLinks.
    I deleted the relationship, changed CustID from Primary and then reset a simple "straight line" one to many relationship.

    There doesn't seem to be any difference.

    Thanks on the delete/re-copy idea that should also work to.

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

    Re: Linking entries in a report (Access 2000)

    You won't be able to enforce referential integrity for the relationship between tblCust and tblLinks, so you'll have to be a little bit more careful - you can now enter "links" between non-existing customers. Other than that, it should work fine.

Posting Permissions

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