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
0
Utf
5/23/2010 1:55:01 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1232 Views

Similar Articles

[PageSpeed] 48

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
0
Utf
5/23/2010 3:54:01 AM
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
0
Utf
5/23/2010 12:35:00 PM
Reply:

Similar Artilces:

Printing problem from Excel 2007
Greetings, I am having an issue with printing from Excel 2007. Many users when printing from Excel with get several pages of non-sensical "junk" printed out on the pages. If it actually prints what is on the page, none of the items are in the cells, but look like they are just randomly thrown about the page. I have found that this most often is related to the Calibri font somehow, because I instruct them to change the font to Arial and all prints fine. That being said, if the user converts the Excel sheet into a PDF and print, it prints perfect. This is a great workaround, but kind ...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Advanced Find
Hi All, Does anyone know how the advanced find utility can be used to create "OR" sql queries? Currently, issuing multiple criteria in V1.2 results in an "AND" query only. TIA... Hi! The only possibility for you is to seperate the values with ";" for example: field1 = "a" or field1 ="b" "a;b" but you can not search for field1 = "a" or field2 = "b" -- Christian support@mscrm-addons.com www.mscrm-addons.com Your company for MS-CRM ADD-ONS! GroupCalendar for MSCRM Related Documents for MS...

Cannot access by OWA
Hi there, we�ve two ex2k servers. First in domain 1 with internet connection. Second in subdomain, connected by a dedicated line. Both servers are in same org and site. When I try to access the second server by owa I can�t access the mailboxes there. That�s only possible on the first server (SSL security is enabled). Do I�ve missed to configure something? Thanks for any advice. JK Sorry, found it myself. Just have to configure the server as a frontendserver! "J. Kuenzel" <kuenzelj@yahoo.com> schrieb im Newsbeitrag news:OT3swrqLEHA.3696@TK2MSFTNGP09.phx.gbl... > Hi the...

Access attachments don't work anymore
Outlook 2000's security update makes it impossible to receive MS Access .mdb or .mde files. But I need to do this to earn a living as an Access developer! Other than have the sender rename the file there must be a simpler way to override the security patch. Outlook Express let's you turn off this feature but OL 2000 has no such setting. (The feature is basically worthless anyway - any hacker would know you just rename the file, put your hacker code in it and send it. Access will open ANY file, regardless of what you name it!!) See if the information on the following page help...

Splitting 1 cell w/2 words into 2 separate cells
Hey everyone! I have a listing of fantasy football players listed as (Firstname Lastname) in one cell. How can I split them up, so the first name is in one cell, and the last name in another? I need this so I can then sort by last name. Thank you! Gary Gary, Select the column, then use Data - Text to columns. Specify a space as the delimiter. Ensure that the next column is currently empty first. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Gary Hopkins" <garyh@somewhereoutthere.com> wrote in message news:41...

This query not giving correct results
I am trying to find the date when we had the most rainfall out of 3234 records, so with the first query to get the maximum rainfall in a month I get 110 records with one null and one '0' value. So this query is saying that out of 3234 records there has only been 108 days when we had rain. We probaly had more than that in one year never mind in 10 years. This cannot be right because we had 24 days of rain in November 2009 but the query only shows 16 for that month!. So how does it actually work? SQL for this below: SELECT Max(tblWXDataLeeds.ReadingDate) AS MaxOfReadin...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

frustrations with Access 2006
is anyone else frustrated with the number of time, when editing a project..., Access 2006 crashes, leaving behind a corrupt, un-repairable file (or somtimes just a corrupt form, never-the-less: frustrating)? short of going back to an earlier version of the program, does any one have ANY suggestions? thanks in advance... mark "Mark Kubicki" <mark.kubicki@verizon.net> wrote in message news:u7Pv4lHDIHA.4880@TK2MSFTNGP03.phx.gbl... > is anyone else frustrated with the number of time, when editing a > project..., Access 2007 crashes, leaving behind a corrupt, un-re...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row I have 12 fields for 12 months of data, I need the average of the sum of all non blank entries. For example 3 months completed, the solution in Excel is (field1+field2+field3)/3 I am looking for method to average the sum in Access One way if you can't change your data is to use a VBA function. I've posted one below. You would call it in a calculated field in a query. Assuming your field names are the abbreviated month names the expression might look like the following. Field: fRow...

pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th pivot table function to reduce the data to hourly I need the date t repeat in the date column. Example: 9/19/2002 0:00 2.7 9/19/2002 0:10 3.7 9/19/2002 0:20 3.8 9/19/2002 0:30 4.6 9/19/2002 0:40 4.8 9/19/2002 0:50 4.8 9/19/2002 1:00 5.6 9/19/2002 1:10 5.2 What I get when I group the Pivot table: Sep 19-Sep 12 AM 2.7 - - 1 AM 3.7 - - 2 AM 3.8 - - 3 AM 4.6 - - 4 AM 4.8 What I want is: Sep 19-Sep 12 AM 2.7 - 19-Sep 1 AM 3.7...

Access 2007 Reports
I'm using an Access 2000 mdb (split ends) in Access 2007. I cannot up- convert because other terminals don't have Office 2007. It works as a database just fine except: When I try to change a (front end) report from the default printer to another specific printer in design view, page setup, it all LOOKS good except it does not save my changes upon return and goes back to the default printer. I never had this problem with earlier versions of Access and the specific printer in questions works fine with other apps and terminals. Any ideas people? Many thanks. JM This is a known prob...

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

IE8 can't access Microsoft Office site when all other browsers can
When trying to access the Microsoft Office page using IE8 I keep getting the "Internet Explorer cannot display the webpage" message. If I use any other browser (Firefox, Opera, Safari, Chrome) I have no problem accessing the page. This happens whether running in normal or No Add-on's mode. Despite how many people keep complaining about all the problems with IE8, neither the MVP's or Microsoft acknowledge that they exist. Asus P5E Intel E8400 Core2Duo 3.0GHz 4 GB PC2-6400 DDR2 Windows 7 Ultimate (with all the latest updates installed) NIS 2010 (all up to date)...

Unable to open Access 95 mdb in Access 97
Hi I am trying to open an old Access 95 mdb in Access 97...I didn't create it but someone who knew the database way back when said that a system.mda file was created for that particular database because it had a lot of personal info in it. (I am not familiar with mda files...the database folder also had a system.ldb file there as well...I deleted that file...but am unsure why it wasn't closed out to begin with) I was able to open Access 97 and repair it...but when I go to open it in Access 97...I get an Automation Error...Cannot find VEENLR3.hlp file. Do you think it may ha...

Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com Stored procedures don’t add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that ...

Backup error Access Denied
I have been recieving the following error message for a while and can't figure out how to fix it. It is only happening on one mailbox. "Backup - \\MBMAIL\Microsoft Exchange Mailboxes Access denied to file Jhon Doe [jdoe]Top of Information StoreSync IssuesServer FailuresMail Delivery (failure jdoe@xyz.com)." Help Please Can you access the mailbox from a mail client? If so, you can use mdbvu to look at the Top of the Information store folder, and delete the message in there. If you cannot access the mailbox, then I have seen it where some users think they are ...

Remove last letter from column
Hi, I have a list of titles and some titles have a letter A or B at the end.. is there a function/formula I can use to remove them if it ends in A or B? For example (my list): Accounting Sr Mgr B Accounts Payable Sr Mgr B Ambulatory Plng Sr Prog Dir A Need it to look like this: Accounting Sr Mgr Accounts Payable Sr Mgr Ambulatory Plng Sr Prog Dir Thanks! This will get rid of the A or B at the end along with the space before it. Assuming the value is in A5: =IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5) kvc wrote: > Hi, I have a list of titles a...

2007 B2 MSOO has encountered a problem
More often then not when I click to open an email in my inbox I get this MSOO pop-up with a checkbox, checked telling me MSOO has encountered a problem and needs to close. Has this been an issue for others and is there a fix. If you have Adobe Acrobat Pro 6/7 (not the reader), go into Control Panel, Add/Remove Programs, click change for Adobe Acrobat Pro and remove the Office & Outlook plugins. Patrick Schmid -------------- http://pschmid.net "Jax" <Jax@discussions.microsoft.com> wrote in message news:A74147F4-CD75-4FF8-98A7-5A18B01A8FDD@microsoft.com: > More of...

Insert Page Numbers on Worksheet in Excel 2007
In Excel 2003, if you wanted to put page numbers on multiple worksheets in a workbook, you grouped the worksheets and then added a header or footer, using the page number function. All of your grouped worksheets would shows its own page number. But in Excel 2007, only my first worksheet is numbered when I do this (as Page 1). What's the problem? Do I actually have to put a page number, one by one, on each worksheet? I cannot replicate your problem with 2007. Grouped sheets behave exactly as 2003 did. After grouping and adding a header of Page 1 did you do a print p...