Linking entries in a report (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks once again Hans – that’s exactly as I needed

Rob

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote