Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    6

    Unanswered: Group multiple entries by month??

    Hey guys,
    I have a table Table Children which records different vaccinations received (children are identified by a unique ID). I want to create a query that will let me see how many P1, P2, P3, Measles 1 and Measles 2 shots were given per month. Each field (i.e. P1, P2, P3, Measles and Measles 2) are preceded by respective date fields i.e. Date of P1, Date of P2 etc.
    I can create individual queries but I don't know how to do it for in one query:

    SELECT Format([Table Children]![Date of P1], 'mmm yyyy') AS Month, Sum(Abs([Table Children]![P1])) AS Penta1_month
    FROM [Table Children]
    GROUP BY Format([Table Children]![Date of P1],'mmm yyyy');

    But suppose I want to see a table which has the months in the column of the left and the different vaccination amounts in the row e.g.

    P1 P2 P3
    August 2009 20 35 40


    thanks for all the help!

  2. #2
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    I guess what you're looking at is (at the risk of getting kicked out of here for academic behaviour, does one get kicked out for academic behaviour) the "normalized" solution for you database design.

    Your table should be child,type of shot, date.

    You might want to transform your current table in the table I suggest by appending (union) the queries to each other. You might even find a query, (or a report) that reproduces your current table structure from the structure I suggest.

  3. #3
    Join Date
    Dec 2009
    Posts
    6
    umm what's a normalized table?

Posting Permissions

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