Querying On MONTH Alone!
Sep 18, 2006
Hi there,
I am getting really wound up with this. It should be so simple!
I need to create a query on a field (actualdateofvisit) and group it by the month to give me a count of all those visits (hence the catchy field name) which occurred each month since the beginning of the project. In other words I want to see:
Month Count of Visit
Jan 43
Feb 54
Mar 78
and so on.
I have tried everything I can think of but cannot work out how to do this? I need to do it in a query NOT a report as I want to port it to Crystal reports to display there.
I am think of something like:
field: Actual Date of vist
table: visits
total: Group By
Criteria: "Month"
Can anyone help me?
Thanks and Regards
Mark
View Replies
ADVERTISEMENT
Mar 1, 2007
I have a query with the following fields:
[Clinic] [Month] [Year] [NewPatients]
Some of the calculations I would like to do are based on the number of new patients that a certain clinic received in the previous month. Does anyone have any suggestions on how I can create a field that has the number of new patients from the previous month?
What I have is this:
PrevMonth: DLookUp("[NewPatients]","ClinicQuery","[Month] = " & [Month]-1)
What I can't figure out is how to account for the fact that I have multiple clinics in the [Clinic] field and each clinic has multiple years in which it was receiving new patients. The PrevMonth statement above works fine for the first clinic listed in the query during the first year it received patients (entries are sorted by month and year), but then just repeats these data for each subsequent year and clinic. Any ideas?
Thanks in advance for any advice!
View 5 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Mar 19, 2012
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
View 10 Replies
View Related
Oct 18, 2005
I have a table full of dates of meetings through out the year...
Example
tMeetingDates
16th August 2005
18th September 2005
19th October 2005
23rd November
-----
i also have a report that i print out each month that has the date of the meeting on it... i currently edit the date manually.
I was wondering if there was a way to automate this facility, so that the report looked to the table of dates and looked for the current Months Date that is stored i the table.
i then want this date to be displayed in the Report.
So in this instance if i am running a report for tomorrows meeting being the 19th October it would display that date in the report....regardless of when i run the report...
Obviously if i run the report on the first of November because the month has changed it would then display the date of the November Meeting...
we only ever have one meeting a month!!!!
Please help
Andy
View 11 Replies
View Related
Aug 9, 2013
Using Access 2010..I have form with a date on it. For this control I have show date picker set to "For dates" and lo and behold I get calendar! I can scroll through this calendar month by month. Great if I just want to go back or forward a month or three. What I'd like to be able to do is scroll through the calendar year by year. Can I do this with the method I'm using at the moment? If not is there a way round it that isn't over complicated?
View 6 Replies
View Related
Oct 13, 2006
HiI have a field that is text which holds the month in form "oct" etc. I need to pass the numerical value of that month to a function so how do I create an expression that will change it (in this instance) to 09.kind regards in advance.peter
View 5 Replies
View Related
May 15, 2006
Hi I'm having a bit of a problem with the sql query below
SELECT [Table 1 Personal Details].Surname, Sum([Table 3 Daily Activity].[Public Holiday]) AS [SumOfPublic Holiday], [Table 3 Daily Activity].[Week Commencing], Sum([Table 3 Daily Activity].[Authorised Absence]) AS [SumOfAuthorised Absence], Sum([Table 3 Daily Activity].Holiday) AS SumOfHoliday, Sum([Table 3 Daily Activity].Sickness) AS SumOfSickness, Sum([Table 3 Daily Activity].[Unauthorised Absence]) AS [SumOfUnauthorised Absence], Sum([Table 3 Daily Activity].[Hours Per Week]) AS [SumOfHours Per Week], [Table 9 Funding].Project
FROM [Table 1 Personal Details] INNER JOIN ([Table 3 Daily Activity] INNER JOIN [Table 9 Funding] ON [Table 3 Daily Activity].[ID Number] = [Table 9 Funding].[ID Number]) ON [Table 1 Personal Details].[ID Number] = [Table 3 Daily Activity].[ID Number]
GROUP BY [Table 1 Personal Details].Surname, [Table 3 Daily Activity].[Week Commencing], [Table 9 Funding].Project
HAVING ((([Table 3 Daily Activity].[Week Commencing]) Between [Enter Start Date] And [Enter End Date]) AND (([Table 9 Funding].Project)="NETWORKS"))
ORDER BY [Table 3 Daily Activity].[Week Commencing];
My problem is that the Surname is displaying for each record.. what I mean is that I want the surname to be grouped so that I get the SUMofHours summed correctly?
here's an example of what I'm getting
SURNAME, WEEK COMMENCING, SUM OF HOURS, PROJECT
Brown,13/02/2006,35,NETWORKS
Brown,20/02/2006,35,NETWORKS
Malcolm,13/02/2006,35,NETWORKS
Malcolm,20/02/2006,35,NETWORKS
Here's what I'm really looking for though
Brown,13/02/2006,70,NETWORKS
Malcolm,13/02/2006,70,NETWORKS
I need the Hours SUMMED with no replication of surname.
Thanks
CJ
Thanks
CJ
View 3 Replies
View Related
Jun 29, 2006
Hey all,
Here is my problem, im working on an old 97 database that is being used by a program and sometimes errors occur through the use of the program and certain tables are not updated properly, so instead of doing this manually I want to be able to query this database through SQL and then later down the line put these SQL queries into a program so it will be more user friendly to edit the database.
So when I try and test one of my queries out, access tells me that I cant make changes to the database because it was created in an earlier version. Now I know i can update the database, but if I were to do that the program that is using the database wont work!
So im a little stuck now, will I have to keep editing the database the manual way or is there another option here? Im wondering (for any java heads out there) If I were to write a java program that would query the database and edit the data inside would that work or would I get back an error?
Thanks in advance!
View 5 Replies
View Related
Jun 16, 2005
Ok, I have a DB of client contacts. I'm drawing the values for a query from a form using [Forms]![Sort]![Contact] in criteria and using the parameter field to give values to each. The query works fine for state, contact and industry- all in conjunction with each other. Where I am having problems is:
1) When I input less then all 3 working parameter values in the referenced form the query will not run. I think i need an SQL string of some sort- doing an if, then relationship, but I don't know how to do this.
2) I don't just have 3 values I want to reference, I have 9 total. On the form one of the problems is that a field is chosen by the end-user using check boxes (allowing only one check between three values, with no default: current client, potential client, not applicable are the choices). However, in the table these are referenced by the values of 1, 2 or 3. Another of my fields is check boxes, but referenced by yes, no. How do I correspond the field values in text to numerical values in the table? (and the yes/no prbly the same I will assume)
3) I have "sectors" as a field option in my Sort form (again the drop down menu to select). There is one field for the choice, however, in my data table I have 4 fields corresponding all back to that one field in the form through the query. This doesn't seem to work at all. The sector choices, in my original Input form, are 8, chosen and recorded into the table using 4 consecutive drop downs. They are the same 8, so sometimes a sector like Agriculture could end up in the Sector 1, or Sector 2, etc field in the table. Some can also be left blank. So in summary of this dilemma: 4 drop downs in input form to 4 data table fields, all being queried through one field on the Sort form, which is tied to my query through a command button, having specific criteria reference text in the form.
Thanks in advance for any help!!!
View 7 Replies
View Related
Aug 10, 2005
Just joined this forum and posting my very first thread.. ^^
I need to create query to pull data from two different tables. This should be really easy query for most of you. Once someone help me set it up, I can do the modifying.
If you look at the image, there are two tables. For each of pf_id (which is unique ID), I need to know the dept_name which is on other table. I know two tables can be linked with dept_id but I don't know how to create query. If you can help me , I would greatly appreciate it~
Result should be like below.
pf_id, dept_id, dept_name
___________________________
wh08051bh-Navy, 01-001, washed hat
wh60516bh-white, 01-001, washed hat
.
.
.
ru11072bh-white_with_navy, 01-002, roll up bucket hat
.
.
dh03871bh-Dk._Blue, 01-003, denim hats
.
.
<img src="http://www.highdots.com/forums/attachment.php?attachmentid=183">
http://www.highdots.com/forums/attachment.php?attachmentid=183
View 2 Replies
View Related
Oct 11, 2005
Is there a way of querying for an astricks? Some data in one of my tables has data that looks like this "A*" or "B*" or "C*", but most of the records are just "A" or "B" or "C"...I just need a query that I can pull out those records. Is there a way of doing this in a query?
Thanks
View 1 Replies
View Related
Nov 4, 2005
I need to take a value from a subform and use it in a query but I don't seem to be able to come up with the correct criteria in the query....
I have so far tried:
[Forms].[frm_Matching].[SubMatching].[txtSingleFixedPayer]
[Forms].[frm_Matching].[SubMatching].[form].[txtSingleFixedPayer]
[Forms].[sub_Matching_single].[txtSingleFixedPayer]
[Forms].[sub_Matching_single].[form].[txtSingleFixedPayer]
[Forms].[frm_matching].[sub_matching_single].[txtsinglefixedpayer]
[Forms].[frm_matching].[sub_matching_single].[Form].[txtsinglefixedpayer]
And probably a load of other versions of the above.... can anyone help out possibly?
The following pulls back the value in code but isn't accepted in the query as a valid criteria:
Forms("frm_Matching").[SubMatching].[Form].[txtSingleFixedPayer]
View 1 Replies
View Related
Jun 2, 2006
OK,
I work for a political party. I work with voter history information. Normally, I am doing queries in order to bring up particular voters, and eliminate others.
Example - the basic table I work with contains the names, addresses, affilitations, and voting histories of everyone in my county. I normally query to bring up voters of a particular party, or people who voted in a particular election or number of elections or both. I more or less sort voters out, but in a positive manner, by creating tables with voters who did a particular thing.
For example: out of all the voters, I just want Democrats who voted in two of the past three primaries. I would then get a table that contained just those voters and no others.
Today, though, I was asked to create a table that would exclude voters based on their behavior, exluding voters who did a particular thing but showing everyone else.
Example, I have a request for all of the voters in the county, except those who voted in the 2004 and 2002 primaries. I know how to create a table with those voters and no others, but I don't know how to query for everyone but those voters. Can anyone help?
View 2 Replies
View Related
Dec 4, 2006
Hi all,
I have a query that is supposed to get a time value from a field and enter a string into a new field based on the time it gets.
this is what i have. Its my VERY first query ever and so i'm not sure of the syntax. I doesnt work though so i'm assuming something must be wrong. Can anyone help?
iif((hour([request date])>12)and(minute([request date])>30)), "After 12:30", "Before 12:30"
Cheers,
Spinkung. :)
View 1 Replies
View Related
Feb 15, 2007
Hi all,
Is it possible to create a query that looks over two different databases for information. i.e I have two departments at work that have seperate databases and I need to be able to search for data in both of them on the same query.
Any help would be greatly appreciated.
View 2 Replies
View Related
Mar 1, 2007
Hi, I'm having some issues querying a sub form.
I'm using a combobox (in a subform) which supplies some search criteria. I'm using the query Like [Forms]![Employee]![Course Subform].[Form].[Combo12] & "*" but its simply not working.
Can anyone see any glaringly obvious mistakes?
View 8 Replies
View Related
Mar 13, 2007
I hope someone can help with this query I am having difficulty with. The table I am querying has several expiration dates and Yes/No Fields
Listed below is what I need and the query needs to return the name and data if only one or all of the following applies
Name
"A Date" is before today
"B Date" is before today
"C Date" is before today
"D Date" is before today
"A Yes/No" = No
"B Yes/No" = No
"C Yes/No" = No
In other words, if all dates are = to today or later and Yes/No Fields are = Yes - I don't need them in the results.
I have tried this several ways using AND and OR but either way my results are ALL the records. not just the records that meet any or all of the above criteria. Maybe it's too much to ask? :o)
Thanks in advance for your help.
Terry
View 3 Replies
View Related
Jan 20, 2008
Hey.I know you can create queries in access to find fields that have a value between 3 and 7 in them for example but how would you create a query that retreives fields with values between 2 dates for example between 12/10/07 and 11/01/08Btw the dates are inday/month/year
View 1 Replies
View Related
Apr 5, 2006
I need to select the last 20 records from a databse that have over 1.25 MILLION records.
Obviously the usual stuff like order by is breaking the server with buffer issues.
Anybody any ideas of how to get around this?
View 2 Replies
View Related
Aug 3, 2006
Hi everyone,
I have a DB that has over 180,000 records with zip code as one of the fields.
I would like to know how do you query the 1st 3 characters? For example, if zip is 95127, I want to query and retrieve records for the first 951.
I seen this on another thread, but can't find it. THanks in advance guys.
View 1 Replies
View Related
Jun 20, 2006
I have a database where the primary key is a field for pass numbers. Many of the pass numbers begin with zeros (example: 0023456). I changed the table property for the pass numbers to text so the zeros would be recognized. However, I have a form based on a query to search this pass number field. How can I get the query to recognize the pass numbers that begin with zeros. When I put in any other number above zero, the pass number satisfies the query and the employee information pops up. Aside from AllowZeroLength and trying to format the text field, I cannot get the query to recognize the pass numbers that begin with zero. Please help:eek:
View 3 Replies
View Related
Jul 14, 2005
Please can anybody help.
I wanted to have a form containing a list of members forenames and surnames in one record. I wanted to be able to input the members reference number and then the forename and surname would be automatically filled in.
I have achieved this through a query based on a table with ten foreign keys for the members. The query uses the members table ten times to join the ten foreign keys.
This works fine and the form runs with space for ten members and I can enter a members reference number in each field and their forenames and surnames are filled in.
The problem is that I wish to run queries with members surnames as criteria to bring out all records containing those members. How can this be done without having to set the criteria in the query in each of the members surname fields.
I know I can do this if I have a separate record for each member in the form
but I really need ten members in each record.
I hope this makes sense and somebody can help.
Thanks in advance
John
View 2 Replies
View Related
Aug 11, 2005
I am new to this forum and a beginner to intermediate with Access.
I was given a database that has 3 tables with data on the capture of alligators. One from 1998-2000, one from 2001-2003 and one from 2003-present. Each of these tables has different field names because they were created by different people on different projects. I need to combine the three tables into one that pulls such data as "date", "time", "size" etc. I don't need all of the fields from all of the tables just select ones and some of the tables do not have the information that I'd need in the final table. I've read through append, update, and make-table queries and am not sure if I can even solve this problem with a query. The error messages I get refer to null values or if I do get a table it has 14000 records, which is way more than the actual number of records.
Thanks for any ideas
View 4 Replies
View Related
Oct 18, 2005
I apologize in advance if this question is completely obtuse, but I'll pose it to the group nonetheless.
My task is to analyze accruals versus actuals for the past quarter (i.e., July-September). What is of particular interest are the variances between the accrued and actual amounts. I have several fields of data, but the common field is Shipping Unit (SU). My initial plan is to upload the tables with the accrued and actual records separately and query SU's to pull in all instances in which there are matching accrued and actual SU's. This is where it gets a bit fuzzy for me. I would export the resulting queries into Excel, unless there is a means to produce the analysis directly from Access.
I haven't used Access in ages, so I don't recall how I might best proceed. Please advise with your thoughts. :confused:
View 1 Replies
View Related
Nov 22, 2005
I have two linked tables as follows:
Table 1
Main Service ID (primary key)
Main Service
Table 2
Sub Service ID (primary key)
Sub Service
Main Service ID (foreign key)
one-to-many relationship
I would like to query the tables, so you search on the Sub Service and it returns all of the Main Services it come under. I would like this query to run off an Input Box that will allow me to type in the Sub Service or a word.
I have managed to get the Input Box, by putting the following code into the criteria :InputBox («prompt», «title», «default», «xpos», «ypos»). But when I type something in the search fails. I have chopped and changed the fields I search on, but can't get it quite right.
Can anyone help me on what fields to choose and what I need to set in the criteria to get the search running properly from the input box?
Also is there any way to program Access or VB to return the results in a message box e.g. **** is a Sub Service of ***** - listing all main services it appears under?
Any help is most appreciated,
Thanks,
Luke
View 1 Replies
View Related
