Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    second use of table (2003)

    I have tblClients, tblConsultants. One consultant can have many clients. consultantID is the primary key in tblConsultants, linked to consultantID in tblClients. A report prints each consultant's clients.

    I want to add the abiltiy to have a "secondary consultant" so thought of proceeding by adding consultant2ID to tblClients and somehow make use of the data in tblConsultants again. However creating a second relationship from tblConsultants!consultantID to consultant2ID doesn't seem to work. What is the best way of doing this? (There is only a single list of consultants in the organisation.)

    Ultimately the aim is to alter the report so that a client will be listed under the consultant if the consultant is either the (original) "primary" consultant, or the secondary consultant.

    Cheers, Roger

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

    Re: second use of table (2003)

    If you are sure that you will *never* need a third consultant for a client, you can use consultantID and consultant2ID. When you open the Relationships window and drag consultantID from tblConsultants to consultant2ID in tblClients, Access should ask you whether you want to edit the existing relationship. Click No to create a new one.

    If there is even the slightes chance that you may need more than two consultants for a client, you should create an intermediary table tblClientConsultants, with fields clientID and consultantID. Each client-consultant combination will be a separate record in this table.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: second use of table (2003)

    Hi Hans, thanks for your reply. I will go with the option of limiting it to the second consultant only. I have created the relationship, now having "tblConsultants_1" in the relationships window, 1 to many.

    Now I need to modify the query that is the record source for the main data entry form. I add a second instance of tblConsultants to the query design window, and it appears as tblConsultants_1, and there is no relationship with tblClients. If I drag the tblConsultants_1!consultantID to tblClients!consultant2ID I end up with zero records in the datasheet view and form. If I delete that relationship I end up with every client record listed 9 times - there are 9 consultants in tblConsultants. I'm clearly missing something here, where am I going wrong?

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

    Re: second use of table (2003)

    It's OK that the second instance of tblConsultants is named tblConsultants_1 by Access - this is necessary because you can't have two tables with the same name in a query. If you would like another name, click on tblConsultants_1, activate the Properties window and modify the Alias property.

    Create a join as described, then double click the join line.
    Select the option to return ALL records from tblClients and only related records from tblConsultants_1.
    Click OK. You'll see an arrow in the join line to show that it is a so-called outer join.
    You may want to do the same for the other join.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: second use of table (2003)

    Thanks, creating this outer join works OK. For the main client form, I have combo boxes for the ConsultantID and ConsultantID2 fields. SELECT qryConsultants.ConsultantID, qryConsultants.ConsultantName FROM qryConsultants WHERE (((qryConsultants.ConsultantName)<>"client")); [identical for both fields] This seems to be OK. (The WHERE clause has a separate use)

    The difficulty I now have is with creating a query on the second consultant field or table. For the original ConsultantID field, it was straight forward to create qryConsultants using all the fields in tblConsultants, so that I could create ConsultantName by joining first and last names, then sorting on ConsultantName.

    The problem arises with adding the second instance of tblConsultants - because tblConsultants_1 isn't actually a separate table that I can add to the design view. I really want to create a second and separate query "qryConsultants2" based on tblConsultants_1 but I don't seem to be able to. I hope this is making sense!

    I attempted to add a second instance of tblConsultants to a query which is the record source for a report that already produces info on which clients belong to the "primary" consultant. The aim of this exercise is to be able to list clients that belong to each consultant, be this in either the primary or secondary field. The design view of the query shows the second table as tblConsultants_1, and adding the fields from it to the query grid show the correct table and field info, all as expected. However viewing the report returns "the specified field "ConsultantFirstName" could refer to more than one talbe listed in the FROM clause of your SQL statement".

    As an alternative approach... Perhaps the easiest thing for me to do overall is to create a second copy of tblConsultants and populate it with data from the original? There is always going to be only a primary and secondary consultant, with no chance of a third consultant. Consultant names won't change often. Maybe there is a way of automatically duplicating data from the first table to the second? This may be an easier way of proceeding, if there isn't a way of proceeding on the track I have so far.

    Any more advice you can offer is very appreciated!

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

    Re: second use of table (2003)

    You shouldn't create a second consultants table, it violates the "rule" that you shouldn't store the same data twice.

    You can define fields as follwos in a query:

    Consultant1FirstName: tblConsultants.ConsultantFirstName

    Consultant2FirstName: tblConsultants_1.ConsultantFirstName

    and similar for last name etc. This way, there can be no confusion.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: second use of table (2003)

    Thanks very much, Hans :-)

    I knew about many-to-many relationships and the need to create an intermediate table, but failed to recognise my situation as an example. However I have gone with the option for no more than a second instance of the table, this fits with the specific application.

    I had attempted to define the fields using tblConsultants_1!ConsultantFirstName - which didn't work. I was leading myself into an apparent dead end on my own.

    Your assistance has resulted in the report ending up functioning as required. Thank you very much for your help.

    Regards, Roger

Posting Permissions

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