Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform to Multiple tables (XP/2)

    I have developed a contacts database that uses three tables (+ others) to categorise the three types of businesses the client normally deals with. For each of the categories (real estate, clubs and general) there is a table linked to contacts, staff who handle the account, the individual business type and a table that uses a query to present recent history - like phone calls, deliveries, etc.

    Each of the categories, I have created a form (see attached) that has buttons to show one of the other categories (eg clubs). The main form has a subform showing the contacts for the current record. As well, there is a 'Details' subform that shows the comments, telephone calls, etc. highlighted in a red rectangle

    My problem is that I want to be able to click a button on any of the forms (the button is named Recent Events... with a red ellipse) and display the 'Details' for all three categories. I feel that by creating three separate tables I have made the task harder. (Another problem I have is that the 'Recent Events' filters the data to show the details for the last two days only and on a Monday there is no data becuase nothing happened on Sat/Sun even though I would like to show the last two working days details.)

    Any suggestions please?
    Attached Images Attached Images

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

    Re: Subform to Multiple tables (XP/2)

    It would be better to use a single table, with an extra field to specify the category (business type). You should be able to create this table fairly easy by adding the category field to one of them, and then executing first an update query to populate this field, then two append queries to add the records from the other two tables, setting the category at the same time.

    If that is not feasible, you can create a union query to combine the records from the three tables for the subform. A union query can be created only in SQL view, not in design view. The simplest form is

    SELECT * FROM TableA
    UNION
    SELECT * FROM TableB
    UNION
    SELECT * FROM TableC

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

    Re: Subform to Multiple tables (XP/2)

    If you want to show details for the previous two working days, you can use this expression in the criteria line:

    Between Date()-2+2*(Weekday(Date())<4)-(Weekday(Date())=1) And Date()-1

    For instance, on a Tuesday this will restrict to the period from Friday through Monday. The expression does not take holidays into account; if you require that, you need to use VBA code and a holidays table.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform to Multiple tables (XP/2)

    Thanks for your prompt replies. (Do you ever rest?)

    On both counts the information is exactly what I needed to set me on the straight and narrow. Do you foresee any other problems if I took the easy path and created the union query? (eg the different tables having the same Primary Key ID - there will be three records numbered "1" for instance.)

    Have I explained it well enough?

    Thanks again, Leigh

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

    Re: Subform to Multiple tables (XP/2)

    You could include a category in the union query. The combination of the ID from the tables and the category would be a unique identifier of each record, and the Category field would let you know from which table records came originally. In SQL:

    SELECT *, 1 As Category FROM TableA
    UNION
    SELECT *, 2 As Category FROM TableB
    UNION
    SELECT *, 3 As Category FROM TableC

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform to Multiple tables (XP/2)

    Thanks Hans

    I'll give it a go...

    Half an hour later:
    But now I have more questions. I thought it would be better to go back to one table with all three classifications of clients (general, real estate and clubs) therefore I have added a column to the original "Clients" table with a lookup value list for the three classifications so that each new entry can be classified. Also, I imagine that the users would prefer to still see three different forms (each is a different colour).

    Would you please tell me the ways I might create a form that filters for one of the classifications? And if you're in a generous mood, how the classification is automatically added simply by the user's choice of form; ie whether it be the Clubs form, Real Estate or General fom?

    Regarding the first part of my question, I know a form can be based on a query and I will try that.

    Regards, Leigh

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

    Re: Subform to Multiple tables (XP/2)

    You can have one form, and use a combo box, or command buttons, or option buttons to switch between the three categories. You could change the background color or other formatting depending on the category selected by the user.

    I would create a lookup table for the categories, for example

    <table border=1><tr><td>[b]

Posting Permissions

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