Prev: M
Next: Search Tables
From: Dan on 22 May 2010 21:55
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
From: KARL DEWEY on 22 May 2010 23:54
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.


"Dan" wrote:

> 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
From: Dan on 23 May 2010 08:35
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


"KARL DEWEY" wrote:

> 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.
>
>
> "Dan" wrote:
>
> > 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
 | 
Pages: 1
Prev: M
Next: Search Tables