TRANSFORM Count([Copy of qryDailyCountOfValidations].Staff_Number) AS CountOfStaff_Number
SELECT [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time]
FROM [Copy of qryDailyCountOfValidations]
GROUP BY [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time]
PIVOT Format([Start Time],"Short Time");
it shows the number of employees that start a shift at a particular time and the time the shift ends, on any day.What i need to show on a graph is the count number of employees starting at x time and plotting the number of hours the employees are on shift until they leave or another employee starts....
-the y axis is the count of employees
-the x axis is the start time of their shift
10
9
8
7 xxxxxxxx
6 x x
5 x x
4 xxxxxxxx x
3 xxxxxxx x
2 xxxxxxxxxx
1
0
00:00 03:15 05:30.........................23:59
So the above mock up says 3 employees start at 00:00 and continue until 03:15 then another employee starts, then 4 employees continue 06:00, then 3 more employees start, 7 all together, then 5 employees leave later etc...
-the level of detail needed is to plot for every 15 minutes.
-The [end time] is not strictly needed, just included to show what columns i have available
achieving this on a graph on an access 2010 report..i have updated a test database so you can see what the data looks like.
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.
I currently have a form from which users can select their name and it will open a report listing events they have attended. It does this through a macro running a filter. What I would like to happen is the same criteria that is being used to filter the report be used as criteria for a different (unfortunately) query that can then generate a graph in the report as well.how to get the criteria into the second query for the graph.
I'm currently working on a project that calls for a graph that is based on an equation which the users can change while in the database.
the equation is basically an annual compound interest formula: 65*(1+x)^([YearNumber]-1), where x is the rate of change as a decimal, and what the users can change.
I've tried to use a simple query with this equation as one of the expressions, where the x is displayed as [Rate as decimal], but when I do this, I get an error on the table: the database engine does not recognize [Rate as Decimal] as a valid field name or expression.
I've also tried tying the equation to an unbound text box on a form called "RateasDecimal", with a button that opens the form with the graph on it, but I get the same error - except [Rate as Decimal] is replaced with "forms!form1!RateasDecimal".
is it possible to create a graph like this in access.
I have a bar graph on a report in ms access that represents 4 different risks ie count on the y axis and risk type on the x axis (low, moderate high and extreme)
Question: How do i get the each bar to automatically represent the colour based on the risk colours below?
Low: Green Moderate : Blue High : Yellow Extreme: Red
I have a report that contains 5 graphs, one for each day of the week. I run this report daily. When I run the report on a Monday, only the Monday graph displays data and the other graphs are blank as there is no data for that day just yet. Is it possible to hide these graphs if there is no data? I did find one suggestion to create a text box with the following code
Code: Private Sub Report() If [SumOfWed] = 0 Then Me.Graph24.Visible = False Else Me.Graph24.Visible = True End If
This does't work for me.Graph24 is the name of Wednesdays graph and the row source for the graph is TRANSFORM Sum([Wed]) AS [SumOfWed] SELECT [Machine] FROM [qryShiftDays] GROUP BY [Machine] PIVOT [Shift];
I currently have a report that generates information based upon a query. The report is broken down into 3 subgroups (Temperature --> Vzip --> VDD). The information within each subgroup seems to be correct as the report cascades through each grouping, however the graph does not update with the new information and instead just copies a clone of itself throughout each Temperature group in the report.
That being said, the first graph that is generated is correct for that group (Temp: -55)... but the report simply copies that graph into the next temperature categories.
I've attached a .pdf of a report generated to visually show what I am referring to, ** notice it replicates the same graph for each group **
How to change the Title of a graph with code. By title I mean the box on the graph that automatically displays the name of the table used to create the graph in the chart wizard. I've tried numerous bits of code found in different forums but they all failed.
Access 2010 OLE Class: Microsoft Graph Chart Class: MSGraph.Chart.8
I want to be able to take two separate queries to create a 2 line graph. Also to be able to spice the graphs up some. The ones I have done, single line, even seems dull.
When I make the column graph the data seems to not be in any order. The report is in order by the number of days in ascending order.
How can I have the columns with the largest number show up first and the columns get smaller. I would like the largest column all the way to the left, proceeding the next one in size so it goes from largest to smallest.
I have set up a form on which there is a graph which draws data from a query. I have set up a text boxes to take in the Y axis min, max and interval values so the user can customize the graph according tot he range coming out of the query. This all works fine and is perfect, however, i also need a report and set up a report with the same graph which can be printed to pdf, however, i cant get the y-axis to adjust like i do with the one on the form,
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I am editing a database that provides the option of creating custom reports, where the user can input a date range of their choice and receive aggregate data for that time frame. Although all of the numbers in the report are correct, I am having trouble with a chart that I inserted into the report.
Specifically, if the date range requested spans 2 calendar years (i.e. April 2014 through January 2015), the data for January 2015 appears at the beginning of the year (so the chart x-axis is for Jan through Dec, and the Jan 2015 data is showing up in Jan (as if it was 2014, not the end of the given range in 2015). When I try with smaller time frames within a calendar year, it adjusts just fine (i.e. shrinking the window so just March-May is displayed on the graph).
How to adjust the axis so that it properly records the data range- so that it would start the axis with April and end in January, for example?
I am still trying to get a hang of development in access 2010.
I would like to design a form with a listbox or a combobox which holds all 8 of my reports (a table has all the reports), with a Print and a Preview view buttons. In addition, the user must be able to select if they want to view the report by month, quarter and the year in question.
How do i have a specific report print or previewed based on the value selected in the listbox or combobox and the date criteria.
I have a queries that do all the calculations and dumps the output to Query X for all different types of customers. At the moment I am required to generate a report for each of the customers and send it to them, manually.
End Goal:Initiate a Macro (at a given time at a given frequency) that would run a process to generate different reports for all different types of customers using a standard report template. I am also trying to avoid having to create a report for each customer (as the customer base grows, the report count will grow) so looking at something that would look into Query X and generate # of reports depending on number of customers.
I'm working on a project where I must save roughly 1000 individual access reports based on the group to a specific folder in pdf format.
Problem:The code I found on this site works, but not exactly the way I need it to. Using the current code (pasted below) all of the pages of the report are being saved to pdf for each group, instead of just the single page. For example, all 1000 pages of the report are being saved to each pdf file, when I only want the 1 page for each of the groups.
I believe that the problem lies in the filter for the Open Event code, but I don't know how to modifiy it to make it work the way I need.
CODE:
Option Compare Database Public strRptFilter As String Private Sub Report_Click() Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE] FROM [qryWty&PendingData] ORDER BY
I have been given a set of pricing tables in Excel that, based on the type of work done, show a dollar amount for time ranges. In other words, if WorkType = A, the appropriate pricing table is referenced and the amount of time in minutes is found within ranges ("0 - .3333", ".3333 - .6500", etc) shown in hour decimals (.3333 = 20 minutes), then an associated dollar amount is reported back. (I know... very awkward, but not my choice). The time ranges are not consistent in size either within a particular pricing table nor across tables. The person using these tables is doing this manually now.
What I need to be able to do is enter the WorkType and duration, and get the dollar amount from the table, even though the duration is not going to exactly match the value in the table. In Excel's VLOOKUP, you can put in amounts that not exact, and the function finds the highest value less than your entry. Can Access do that? I tried reading up on DLOOKUP, but I don't think that is what I need (or maybe I just don't understand it). I'm not sure of an efficient table setup or how to get the correct output.
I have two fields. A barcode field will have several instances of the same data, and the other field has different dates and times when it was scanned. I need to be able to query the barcode field and only return the record with the latest time. Can this be done in a query or do I need to do something else? Here is a sample of data I am working with. I attached a small sample of data as an example.
I'm making a dispatch call database that will be sorted at the end of the month based on call types and which shift that call was originated on. Our shifts are 6a-2p, 2p-10p, 10p-6a.
I am figuring that the time would be converted to minutes and then the display would be figured based on which times the minutes occured between. IE: the call origintaed at 0100hrs = 60 minutes. It would figure that 60minutes translates to "Night Shift".
How would I convert time into minutes and how would the if/then be established?
I'm trying to find a solution to a problem. Is there any way using VBA to generate a random number, based off the current system date and time? The theory is that a random number seeded by the system date/time will be truly unique and the possiblity of it ever occuring again will be nearly nil.
Or is there a better way to create unique ID's (such as member ID's, or something similiar) based off the system date/time that can be used as a primary key in a relational db.
How do you run a procedure that activates at a set time? I want to run a procedure that moves a log file to an archive directory at midnight (or at anytime after this when the PC is next turned on). Thanks for any help... Mat
I will simplify the situation as I can , I have one table in my DB with the following fields :
ID , APP_DATE , APP_TIME , DETAILS , APP_STS
I want to have a pop-up form 5 minutes before the appointment time !!! (That is it)
- I know it's better to manage the appointments within outlook. - I do not want to connect my DB with outlook appointments. - I want the reminder form to have a "snooze for 5 minutes" button and "Done" Button to change the appointment status (APP_STS) to done. - I think we are going to use on timer event (which is totally new for me)
