MS Access 2007 Query Separating Info into months columns

[Dan]

Hello,

I have a contact table (Tblcontacts) that has fields (FldID[PrimaryKey,
autonumber], FldFirstName, FldLastName, FldPhone, FldStreet, FldCity,
FldState, FldZipcode, Fld0to5, Fld6to18, Fld19to40, Fld41to60, Fld60plus,
FldComments). 2nd Table is TblVisits. Fields are (FldID2[Primarykey,
autonumber], FldVisit[Date], FldID[ForeignKey][number],FldVisComments)

The fields with numbers receive a number by the number of Family members in
that age catergory. (Fld0to5 with a value of 3 means 3 family members are in
that age bracket. The cotact info is for the head of household. The other
family memberss are only referred to by their age in the appropriate field.

I use a form(FrmContacts) with subform(fldVisits[these are the dates the
family visits the food pantry])

I need to create a query that lists each head of household) in the first
column, columns 2 through 13 are according to each month of the year,
populating the number of times each Captain/team visited the food pantry in
each month then in column 14 total the number of visits for the whole year.
Columns in my report will be Name(totaling all Contacts), Jan, Feb, Mar, Apr,
May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Total(Total Visits)

The Part i am stuck on is sorting the total number of visits per month for
each contact then using a grand total for the year. Suggestions on how to
accomplish this would be very much appreciated. I am fairly new to Access,
so any references or pointers to examples are apprciated as well. Thank you
for your time!


Dan

 

[KARL DEWEY]

Try this ---
TRANSFORM Count(TblVisits.Date) AS CountOfDate
SELECT Tblcontacts.[FldLastName] & ", & Tblcontacts.[FldFirstName] AS [Head
of household], Count(TblVisits.Date) AS [Total Visits]
FROM TblVisits INNER JOIN Tblcontacts ON TblVisits.FldID = Tblcontacts.[FldID]
GROUP BY Tblcontacts.[FldLastName] & ", & Tblcontacts.[FldFirstName]
PIVOT Format(TblVisits.Date, "mmm") IN("Jan", "Feb", "Mar", "Apr", "May",
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");

 

[Dan]

Very interesting solution. I am working my way through it. Thank you very
much for taking the time to assist me on this problem. I will post back my
progress.

Cheers
-Dan

Try this ---
TRANSFORM Count(TblVisits.Date) AS CountOfDate
SELECT Tblcontacts.[FldLastName] & ", & Tblcontacts.[FldFirstName] AS [Head
of household], Count(TblVisits.Date) AS [Total Visits]
FROM TblVisits INNER JOIN Tblcontacts ON TblVisits.FldID = Tblcontacts.[FldID]
GROUP BY Tblcontacts.[FldLastName] & ", & Tblcontacts.[FldFirstName]
PIVOT Format(TblVisits.Date, "mmm") IN("Jan", "Feb", "Mar", "Apr", "May",
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");

--
Build a little, test a little.

Hello,

I have a contact table (Tblcontacts) that has fields (FldID[PrimaryKey,
autonumber], FldFirstName, FldLastName, FldPhone, FldStreet, FldCity,
FldState, FldZipcode, Fld0to5, Fld6to18, Fld19to40, Fld41to60, Fld60plus,
FldComments). 2nd Table is TblVisits. Fields are (FldID2[Primarykey,
autonumber], FldVisit[Date], FldID[ForeignKey][number],FldVisComments)

The fields with numbers receive a number by the number of Family members in
that age catergory. (Fld0to5 with a value of 3 means 3 family members are in
that age bracket. The cotact info is for the head of household. The other
family memberss are only referred to by their age in the appropriate field.

I use a form(FrmContacts) with subform(fldVisits[these are the dates the
family visits the food pantry])

I need to create a query that lists each head of household) in the first
column, columns 2 through 13 are according to each month of the year,
populating the number of times each Captain/team visited the food pantry in
each month then in column 14 total the number of visits for the whole year.
Columns in my report will be Name(totaling all Contacts), Jan, Feb, Mar, Apr,
May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Total(Total Visits)

The Part i am stuck on is sorting the total number of visits per month for
each contact then using a grand total for the year. Suggestions on how to
accomplish this would be very much appreciated. I am fairly new to Access,
so any references or pointers to examples are apprciated as well. Thank you
for your time!


Dan