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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|