A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

MS Access 2007 Query Separating Info into months columns



 
 
Thread Tools Display Modes
  #1  
Old May 23rd, 2010, 02:55 AM posted to microsoft.public.access.queries
dan
external usenet poster
 
Posts: 1,408
Default MS Access 2007 Query Separating Info into months columns

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
  #2  
Old May 23rd, 2010, 04:54 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default MS Access 2007 Query Separating Info into months columns

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

  #3  
Old May 23rd, 2010, 01:35 PM posted to microsoft.public.access.queries
dan
external usenet poster
 
Posts: 1,408
Default MS Access 2007 Query Separating Info into months columns

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.