Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Query (2003)

    Hello,
    I have an enrollment table that lists a member's effective date and term date. I am trying to take that data and place them as members in specific months and possible to see if they are in consecutive months. An example of the data table is below and I would like to be able to do without a computed field for each month.

    Member Effective Date Term date

    1 1/1/2008 3/31/2008
    1 6/1/2008 9/30/2008

    Member would be eligible in Jan, Feb, Mar.Jun,Jul, Aug, Sep.

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

    Re: Complex Query (2003)

    Can you explain what you want the output to look like?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Query (2003)

    Edited by HansV to improve presentation of data

    Something like this....
    <pre>Member ID Jan-08 Feb-08 Mar-08 etc
    112121 1 1 1
    </pre>

    Something that would list the month and year at the top and count one if enrolled for that month and 0 if not. I know I could do would a computed field for each month but there has to be a better way.

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

    Re: Complex Query (2003)

    You need to create a table listing the months.
    You can then create a crosstab query based on your table and the months table, as in the attached database.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Query (2003)

    Thanks Hans and I don't think I would have ever come up with that solution. Also, I am not accustomed to seeing tables not linked like that. Are there some articles that talk about non-linking of tables that I can read about? Thanks

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

    Re: Complex Query (2003)

    If you add two tables to a query without linking them, the query will return all possible combinations of a record from the first table with a record from the second table. So if one table has 7 records and the other has 4 records, the query would return 7*4 = 28 records.
    But in this specific example, there is a condition on the month field in tblMonths that "links" this field to the date fields in the other table, to restrict the records being returned.

    I don't know of any articles that explain the use of non-linked tables in a query.

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

    Re: Complex Query (2003)

    I just posted a sample database with a query that combines two tables that are not linked directly. It's used to print multiple copies of a record in a report. See <post:=757,451>post 757,451</post:>.

Posting Permissions

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