Query Graph Two Count Value Comparison By Date...Attached Screencaps
Jul 24, 2005
:eek: I have tried almost everything and I still can get this to work. I thought I had it with this attempt until I inputted October data and found that Access was sorting it by Number vs. Date.
I am simply trying to compare the number of tickets opened each month to the number of tickets closed each month. I also need to address how a report will appear if no tickets were opened or if no tickets was not closed in a month.
Incase the SQL in screencap is too difficult to read:
Query: qryTotals_Assigned_And_Completed - This calls the following two queries.
#############################################
SELECT qry_AssignedByMonth.MyAssigned, qry_AssignedByMonth.Count, qry_ClosedByMonth.MyClosed, qry_ClosedByMonth.Count, qry_AssignedByMonth.Month, qry_AssignedByMonth.Year, qry_ClosedByMonth.Month, qry_ClosedByMonth.Year
FROM qry_AssignedByMonth INNER JOIN qry_ClosedByMonth ON qry_AssignedByMonth.Month = qry_ClosedByMonth.Month
ORDER BY qry_AssignedByMonth.Month, qry_AssignedByMonth.Year, qry_ClosedByMonth.Month, qry_ClosedByMonth.Year;
#############################################
qry_AssignedByMonth
#############################################
SELECT (Format([DateAssigned],"mmm"" '""yy")) AS MyAssigned, Str(Month([DateAssigned])) AS [Month], Str(Year([DateAssigned])) AS [Year], Count(*) AS [Count]
FROM tblRequests
GROUP BY (Format([DateAssigned],"mmm"" '""yy")), Str(Month([DateAssigned])), Str(Year([DateAssigned]))
ORDER BY Str(Month([DateAssigned])), Str(Year([DateAssigned]));
#############################################
qry_ClosedByMonth
#############################################
SELECT (Format([DateClosed],"mmm"" '""yy")) AS MyClosed, Str(Month([DateClosed])) AS [Month], Str(Year([DateClosed])) AS [Year], Count(*) AS [Count]
FROM tblRequests
GROUP BY (Format([DateClosed],"mmm"" '""yy")), Str(Month([DateClosed])), Str(Year([DateClosed]))
ORDER BY Str(Month([DateClosed]));
#############################################
I have a graph which queries data from a table, I need the graph to show the dates in the table with the correct results specific to that date.
However on the graph its displaying dates not in the table so its plotting the results out wrong, I need it to only show the dates in the the table on the bottom axis.how to do this.
I have a field [CurrentDay] of type Date/Time. The values for this field are entered through the selection from the date picker. However the time of the day always come along with the date - even if it is not seen. This creates a huge problem when making a query based on this field because no results will be returned unless the time is included with the date in the criteria.
The other part to the problem is that I cannot find a built in function in the expression builder to generate the current date without appending the time - now() certainly does not do that - even if the time is not seen it is there.
Hi all- I'm creating an update query.I have a field [dateLastEdit] that is stored as text, I have to compare it to a date entered on a form by user [date_from] (I need to select all dates greater than this) but I'm running into problems:when I use date_from in text or date format and compare it to dateLastEdit in text format the comparison is made in numeric order(so 12/02/2006 is selected as well as 12/02/2005 when date_from is 12/01/2006)SELECT PTSData.LastEditDateFROM PTSDataWHERE (((PTSData.LastEditDate)>=[Forms]![chMgmt_frm]![date_from]));when I use date_from in date format and compare it to dateLastEdit converted to date format I get "expression typed incorrectly or it is too complex to be evaluated" msg.SELECT DateValue([LastEditDate]) AS Expr1FROM PTSDataWHERE (((DateValue([LastEditDate]))>=[Forms]![chMgmt_frm]![date_from]));[LastEditDate]'s format is always mm/dd/yyyy hh:mm:ss, but as text, not date data type.I've tried CDate with same results, isolating it in its own query to test it, using hardwired dates as criteria, but no joy, am I missing something obvious?forgot to add- I've used all the above with left also to select only date portion with same results
I'm trying reference a dcount on two fields, one is a string and works fine, the other is on a date which does not.
I've investigated the problem and found a couple of references to making sure I use a # symbol to reference the date but I think the thing that's throwing the comparison out is that the date field stores time as well, even if you can't see it?
I thought should count the records in the healthcheck table that match the check_date field against the first_health field on the current form, but I keep getting a 0 count even though the dates seem to match.
I have a date field (DueDate) that I am comparing against the current date (Date()). When the current date is greater than the DueDate I filter just those dates. I have done that without any problems. Now I am trying to figure out how to compare that filtered information to show that the DueDate is either over 30 days, over 60 days, or over 90 days overdo. If it shows up on either one of those lists I do NOT want it to show up on the other lists. I also want to have a counter by states. For example:
Current Date DueDate State a. 9/22/14 2/1/14 GA *(90) b. 9/22/14 8/20/14 WV *(30) c. 9/22/14 7/1/14 WV *(60) d. 9/22/14 8/19/14 GA *(30) e. 9/22/14 7/31/14 GA *(30) f. 9/22/14 3/1/14 WV *(90) g. 9/22/14 7/20/14 SC *(60)
Okay, me again, on my DAP (Data Access Page) I have a piece of JavaScript that tells my page onload compare 2 dates. It's straight forward and all and my code works. That's actually where the problem is... When access loads my DAP up it builds the page, executes the javascript and THEN populates my fields with all the records. So my comparison javascript will always execute BEFORE the data loads into my DAP...
<body language=”javascript” onload=”colorBox();”>
<SCRIPT LANGUAGE=”javascript”> Function colorBox(){ If (Quarter1DueDateP1 >= Quarter1CompleteddateP1){ Box3.{background-color:Green;} } Elseif (Quarter1CompleteddateP1>=Quarter1DueDateP1){ Box3.{background-color:Red;} } Else { Box3.{background-color:Yellow;} } }
I recently (temporarily)took over a position that uses an Access database that does not work properly, and I'm stumpped on how to fix it.
The query is supposed to pull all data where the "Date Overdue" field is less than today.
"Date Overdue" is a calculated value that pulls from the field "Date Input", which is in a text format (DDMMMYY) Such as 03NOV13. It is 8 days after the date input.
It prints out like this: "Monday, November 11, 2013" which is 8 days after the 3rd.
"Date Overdue" is set to this value:
Code:
DATE OVERDUE: DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8 "Date Overdue" has the criteria "<DateValue(CDate(Now()))"
I'm not going to go into all the different steps I've taken to try and get this to work because I've toyed with it a lot..
The output that I always seem to get is a mixture of all records that are available, before and after today's date, I just wanted those that are less than today.
I suspect that the date values that are shown in the query aren't true dates because when I click on the filter button it gives me this error:
"Syntax error (missing operator) in query expression 'DATE OVERDUE' "
NOTE: I'd like to add that this is just a regular Select query.
Code: SELECT DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2)) AS [PRODUCT END PERIOD], DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8 AS [DATE OVERDUE], [QBR ON EQUIP].DATEINPUT, [ALL ERRORS].[ERROR STATUS],
As shown in the attached database, I have a multiple criteria query with a front end (see frmIssue). I am trying to add a keyword search on the field 'Issue' by having an unbound textbox in frmIssue and linking it with a 'Issue' field in the query 'MyQuery'.
An example search would be looking for an issue with the keyword 'misfiring' in project 'A'.
However, having tried the Like expression on the field 'Issue' a few times, I'm still having trouble making it work. Any suggestions would be greatly appreciated!
I do have the following problem, and - please do tell me- if I don't see the obvious.
I changed to Office 2007 and am busy changing an Access Application. In the course of that, I included some .gif files in an attachment field, which were hitherto stored in external files and loaded when required for display.
Say we have a company table (OPR_Operator) and a country table (GEO_Country). The country table has a country flag (Field GEO_Country.Flat) as attachment file.
If I create the following query:
SELECT OPR_Operator.OPID, OPR_Operator.Operator, OPR_Operator.Country, GEO_Country.CountryName, GEO_Country.Flag FROM OPR_Operator INNER JOIN GEO_Country ON OPR_Operator.Country = GEO_Country.CountryCode;
I can edit existing records in a form without any problem. If I change e.g. the Country field in OPR_Operator, the new flag is displayed.
The problem occurs, when I want to add a new record. No way to do that. As soon as I enter the Country, I get the following error message:
The current field must match the join key '?' that serves as the one side of one-to-many relationship. Enter a record in the "one" side table with the desired key value, and then make the entry with the desired join field in the "many-only" table.
I appreciate that this reads like a beginner's error. The funny thing, though is, that I entered a country code, which DOES exist in the GEO_Country table.
I have a table that includes client account information. I want to create a query that will select all account numbers where the last updated date is less than or equal to a number specified by the user.
Table (ClientRequests)
AccountNumber LastUpdated 76869 5/10/2005 9:49:38 AM 22151 10/29/2005 10:35:23 AM
so for example: I would like to display all accounts where the differrence between todays date and the LastUpdated date is <=10. Where 10 would be a user defined number.
I have a scheduling database that my company uses to manage our customers deliveries. We would like to make this database more intelligent by adding some logic and I am not quite sure how to proceed.
On the form frmDelivery, the user inputs all the header information for the delivery, in the subform subfrmLotInfo, they input the houses that will be delivered, the delivery date, and some other miscellaneous information.
When the user inputs the delivery date, the system should count back a number of days based on other criteria and set a build date (when we should start building the cabinets, typically 5 days before the delivery date). Then I need the database to run a query against this date and determine how many boxes we have scheduled to be built already on that day, if it exceed a certain number than I need to it to search an available date close to that date that has not exceeded our production capacity and schedule the lot for that date.
This needs to happen as well if the user changes the delivery date at a later time. Our customers change delivery dates constantly. This will help us schedule our manufacturing plant better and reduce the possiblity of scheduling more than we can actually accomodate.
I have a table tblPrdCapacity that holds how many boxes we can build on a given day. It hold Date, PrdCap (number of boxes). In the subfrmLotInfo, the field BoxesBuilt tells how many boxes will be built for that Lot.
Any info would be greatly appreciated!! I have been tasked to accomplish this as soon as possible.
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:
I want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.
I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].
Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?
I am still confuse after reading threads on producing running sum on queries. help!
This query is to produce a time line graph of Progress Tasks with 3 main categories - New, Completed and Outstanding. So I got months on the x-axis and count on the Y-axis.
sample data. say we look back for jobs in the month in feburary Date Added; Date Completed ; category 11/1 ; NIL ; Outstanding 11/1 ; 1/3 ; Outstanding 11/1 ; 14/2 ; Completed 1/2 ; NIL ; Outstanding 1/2 ; 1/3 ; New and Outstanding 1/2 ; 14/2 ; New and Completed
so the total count of the categories for Feburary New = 2 ,Completed= 2, Outstanding=4
I have a query that pulls up the sum of records that holkd a date value between two dates, and groups them by the person that created them.
How can I show these results in a graph? that will update every time a user clicks a buttons (as they may wish to change the two dates to search between)...
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code: SELECT [tblBalances].[BalanceDate] AS AsOfDate, (SELECT COUNT([tblIssues].[IssueID]) FROM [tblIssues] WHERE [tblIssues].[Flag] = True AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code: (SELECT TOP 1 [tblComments].[UpdatedWhen] FROM [tblComments] WHERE [tblComments].[IssueID] = [tblIssues].[IssueID] AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate] ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
I am trying to generate a report that is based off of a query. The query has a form filter that it needs to filter the data. I keep getting a jet engine error and couple others.
The form has year, start week, and end week on it. I can get the query to work fine. When I try to open the report, Access says it doesn't recognize the " [Forms]![frmUptimeFilter]![StartWeek] " as a valid field name or expression.
How to build query to give daily balance across bank accounts? (to then plot in a graph)
Assumptions:
* There is a table TRANSACTIONS which includes columns TRANS_DATE, AMOUNT and BANK_ID. It does NOT include a column for balance. So current balance for a bank account is the sum of the AMOUNTs for that BANK_ID for example. Balance on date XX will be the sum of all AMOUNTS for that BANK_ID for all TRANS_DATE's prior and including the date XX. * Table BANKS which has BANK_ID and TITLE
Would like a query that gives: Supply StartDate and EndDate for the query:
My database is to track customer incidents. Can anyone tell me if my relationships look ok? I'm a remedial access user to any input would be appreciated. Thanks in advance for the help.
I would have thought this was easy, but I keep getting an error. I have a form with a save button. When the save button is clicked, I want it to check if user has attached an OLE object to a bound object frame. If they have, I'd like a checkbox to be true. Here's the code I tried: If Attachment.LpOleObject = 0 Then Attach.Value = False Else Attach.Value = True End If This seems so incredibly straight forward and it does work if there is an attachment. If there isn't I get the message that my database can't retrieve the value of this property.
