Query Showing Different Results When Moved To Chart In Report
Dec 23, 2014
I am trying to use this query. It gives me correct results as query. However when I make chart with query on a report it doesn't show correct data and eventually stops making chart
SELECT qry.txtRC, Count(tbl.txtRC) AS CountOftxtRC
FROM tblMain AS tbl, qryRC AS qry
WHERE (((tbl.txtDepartment)=[Forms]![frmRC]![cboDepartment] Or [Forms]![frmRC]![cboDepartment] Is Null) AND ((tbl.txtZone)=[Forms]![frmRC]![cboZone] Or [Forms]![frmRC]![cboZone] Is Null) AND ((tbl.txtRC )=[qry].[atnRC ID]) AND ((tbl.date) Between [Forms]![frmRC]![startDate] And [Forms]![frmRC]![endDate]))
GROUP BY qry.txtRC , tbl.txtRC
HAVING (((Count(tbl.txtRC )) Is Not Null)) OR (((Count(tbl.txtRC )) Is Not Null))
ORDER BY Count(tbl.txtRC ) DESC;
Colm and Baxter, you've been great helps to me so far, and I will include you in the special thanks in my programming credits. I ask your help once more-
My program tracks how many calls are received per day at the office. I have a report showing a chart (thank goodness for wizards ) that lists the calls per day. This all works fine, and I was extremely proud of myself for doing so. However, after thinking about it, I realized that after a while, my client might not care to see ALL of the calls per day ALL of the time, and might wish to see maybe a week's worth or a month's worth at a time. I was thinking I could solve this by using a form to enter "from" and "to" information, but I'm not quite sure how to apply this so it would work with my report.
Also, if I can get this to work, on this same form I would like to have an option field that the user can select so that the usual options (today, this week, this month, this year, all) require only a click, rather than having to figure out what days are in this week (a tedious task, I know, but we're going for efficiency here ) but they also have the option of seeing specific dates they want.
The fields that I'm using for this are very simple- tblCustomer.CallDate and tblCustomer.LeadType, where CallDate is just a date and LeadType is a string from a lookup table that is either "Call-in" (the one I'm tracking) or "In-field".
I know this sounds like a lot of coding, but I'm sure if you could get me started, I could figure out the rest myself. It's just that the way Access does dates is so confusing to me, and I have midterms coming up and not a lot of time to figure this out by myself from scratch. Anyways, thanks for your help in advance!
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
Does anyone know how to do a query so that the user can find all the things that will expiry at the end of the current month? The user will click on a command button and it will show the results of a product that will expiry at the end of a current month. Have tried with parameters to which the user manually types the end date in and then it will show the results but having problems. Cheers
SELECT Training.ID, Training.StaffPIN, Training.Module, Training.DatePassed, Training.DateExp, Training.Comments FROM TrainingModules INNER JOIN Training ON TrainingModules.[TrngModule] = Training.[Module] WHERE (((Training.StaffPIN)=[Forms]![TrainingFrm]![StaffPIN]));
Does anyone know why this query isn't working? It doesn't throw an error, it's just not showing any records.
The only thing I can think of is: Training.StaffPIN is a long int When it prompts me for [Forms]![TrainingFrm]![StaffPIN] I enter '12177' in the message box, is that taken to be an int or is it actually a text string?
It might not be this as when I use the form [TrainingFrm] to pass the perameter, it's taken from a combobox ([StaffPIN]) where it IS an int, and the query is still blank. :confused:
TrainingModules is a table that stores all the possible modules or subjects that a member of staff can be trained in Training is a table that stores which staff member has passed which module or subject. I want the query to pull all the info on a staff member from Training to be later used as a subform.
I have a totals query that shows results in a chart. It takes a parameter to limit results, by a combobox in a form.
Parameter in the query includes the OR "*" expression, in case someone wants to get the results unfiltered.
The Combobox in the form, has an AfteUpdate event that opens the chart (form) every time its value changes, by the [DoCmd.OpenForm "ChartForm" , acNormal] expression.
I don't know how to make it open the ChartForm when no parameter is selected in the combobox.
I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.
The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?
Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).
A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).
I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...
My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case. Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:
1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler) 2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)
I am fairly new to access, but do have some experience with SQL & PHP.
I am trying to do something which I would have thought was quite straight forward. But I keep hitting a brick wall and all I get is #name? error on my form.
I have a table "tbl_Job_Spec" and a table "tbl_Tool_List".
I would like to have on the form for tbl_Job_Spec a drop down for selecting the tool number from the "tbl_Tool_List", which then automatically puts into a text box on the form the tools location, which is stored alongside the Tool Number Field on tbl_Tool_List.
I've tried loads of things, I have a query setup ("qry_tool1loc") which seems to correctley identify the relevant information, but when I try to reference that query on the form if just comes up with #Name? in the box on the form.
Anybody got any ideas? I'm only at an early stage in what is quite a big project, but to be honest this is about as complicated as it's going to get, so once I can get past this hurdle it should be plain sailing!!!!!
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset Dim strReport As String Set db = CurrentDb() Set qdf = db.QueryDefs("7-ErrorsReport") qdf.Parameters(0) = Forms!frmmain!dt1.Value qdf.Parameters(1) = Forms!frmmain!dt2.Value qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
Access 2010. This has worked before but I don't understand why this is not working for me now. When i select an item in a combo box in a form and click on a button to run a query with the results, the query is blank. If I run the query alone, it prompts for an item, I can type it in and it works. I have even tried the DoCmd on the combo box but still same results. Attached is a dummy down DB. If you run the query, it will prompt, select Paper or Rock, see results. But run the form, the drop down will not show the results. What am I missing?
I am trying to run simple query make a chart in report through this query but its not generating chart?
SELECT [Unsafe Act Unsafe Condition].[Inspection Audit], Count([Unsafe Act Unsafe Condition].[Inspection Audit]) AS [CountOfInspection Audit] FROM [Unsafe Act Unsafe Condition] WHERE ((([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmSafetyReportOut]![startDate] And [Forms]![frmSafetyReportOut]![endDate])) OR ((([Unsafe Act Unsafe Condition].Department)=[Forms]![frmSafetyReportOut]![cboDepartment])) GROUP BY [Unsafe Act Unsafe Condition].[Inspection Audit] HAVING ((([Unsafe Act Unsafe Condition].[Inspection Audit]) Is Not Null) AND ((Count([Unsafe Act Unsafe Condition].[Inspection Audit])) Is Not Null));
I have a chart that shows the amount of complaints every month in the last year of a selected costumer. The goal is to see if the amount of complaints from this costumer is decreasing or increasing.
The problem is that some smaller costumers have months without complaints (in real life that isn’t that bad). But my graph only displays a dot for the months were the amount of complaints is not null. This makes that the graph line doesn’t show the complete picture.
I know that I should be able to solve this with an ISNULL expression. I have tried the following SQL code as Row Source of the graph: SELECT (Format([Complaintdate],"MMM 'YY")), ISNULL(Count([Complaintnumber])),0 AS [CountOfComplaintnumber] FROM [Complaints] WHERE [Complaintdate] > (Date() -365) AND [Costumername] = Forms!Report_complaintscostumer!Combocustumername GROUP BY (Year([Complaintdate])*12 + Month([Complaintdate])-1),(Format([Complaintdate],"MMM 'YY")); The result of this is that all the months with complaints now get a 0 value and still the months without complaints aren’t shown.
What am I doing wrong? Could someone help me solve this problem? Any help is greatly appreciated!
Value 1 which is the number of working Employees and Value 2 which is the total number of Employees
Both are subject to change but my main problem is that Value 2 (Total of Employees) i want to display as 100% in a chart and Value 1 is the percentage of employees utilized.
I have the calculation working fine (Value 1/Value 2).
But if for example i have 35 employees as Value 2, how do i make that show as 100% in the chart?
I get things like 35 or 3500%?
I have played about endlessly changing values etc but just cant get it.
I created a table called weld_performance. it consist weld_id, weld_prod, total_rt, accepted, rejected, and rejection rate. from weld_prod until rejection rate, the type data is number. the rejection rate field size is Single, and Format is Percent.
Based on this table, i create a query called query1. and based on this query1, i create a chart. please see attachment pic001. as you can see the data label on the chart is showing 0.66667. but in my query1, the value is 67%. if i click the value (67%) it's change to 0.66667.
So I guess, the chart is read the 0.66667 value from the query. now what i want to ask is, how can my chart data label is shown 67% instead of 0.66667 ??
I created a Report from a query. The query shows the correct data that should be on the Report. I created the report to sort by Field A and then sum Fields D, E, & F. None of the query data shows up on the Report. I;m stymied as to why I can see data on a query, but the ONLY data that shows up on the Report is Field A. None of the summations show. All are blank with the box outline.
I've created the report 3x thinking I did something incorrect. Whatever it is, I did it 3x!
I am trying to create a report that will show me any record that does NOT have a specific field met. I have a basic query set up to display the information desired, but need it to refine the search to just the records missing a specific part in a different table. The missing information does not have to be shown in the report.
I'm an Access novice. I have a query based report that is based on several tables. All tables are joined by the same field "customer ID," but 1 table is not available under "Add Existing Fields." I cannot figure out why that table isn't available, but I need to add a field.
I have a a table 'Orders' with fields (Order Number, Order Date, CD Number, Card Number).
I would like to produce a query in access 2010 that would allow me to count how many times the CD Number 'Diab190617' has been purchased.
I would like to store the results of this count and counts on other cds numbers somewhere so that I can produce a graph/chart of these counts. How can I do this?
I have two database tables, one of information of members and one of staff. I want to do a query to show all members and staff that have the title of 'Mr' in the database.
There are only 5 staff details in my staff database at the moment, all with the title 'Mr'. There are 20 members details, 13 with the title 'Mr'. When i run the query i get only 8 names, 4 from the staff and 4 from the members.
The 4 staff ones i get are the 2nd, 3rd, 4th and 4th records. The first record of the members field does not have the title 'Mr' and so it appears that this is blocking the first staff one to appear. And the members stop appearing after the 5th record too, so it appears they are stopped as there are no more staff records.
I hope this makes some sense to you, and if anyone could tell me how to get all the records to appear that would be great. Thanks.
I have a query which is made up of several other queries that total different fields (these are a base for a report) sometimes one of the queries will be empty and then the whole query is blank, how can I return 0 when there are no records to count in 1 query so that the results from the others still show?
I have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?
I am relatively new to writing databases in access, I did some a long time ago, but cant remember what on earth I used to do.
I have created a database with all the information I need it and have a query set up that gives different results depending on the information the user has entered
The user decides what the query will display though a form.
I want access to generate a report with the information in it that the query chucks out.
However the information the query gives changes every time depending on the user input.
I have a pretty complex database that uses several queries that all lead to a final query with a parameter (order number). I have a report linked to that query so every time I open the report it asks me for an order number. Once I enter the order number the query is run and I print the report. I am curious if I can create a query that pulls out order numbers (possibly for a date range) and then have a macro or something loop through that query and print a report for each order number. So if the query returned 50 order numbers then the report with the parameter (called Final results) would be printed 50 times.
I have a query that prompts the user for input to generate a report. I would like to in essence copy that same record set and append it to a different table (archive table).
