Queries :: Daily Activities - Group As Weekly Total And Transpose For Reporting

Jun 5, 2013

Query of daily activities spent hours

1) to be group as weekly total
2) then need to transpose it for reporting.

My table fields are Date, SpentHrs and Code (activity code description).

View Replies


ADVERTISEMENT

Trying To Create A Report (calendar Style) Based On Weekly Activities

Jan 15, 2015

I have created a table for a Gym that shows which classes / activities are on which days. You will notice in the image below that i have assigned Boolean to associate a class with a day of the week.

I now am trying to create a Calendar STyle report that GROUPS all activities by the day they are on. So, for example, see below:

As you can see, all of Monday's activities will appear first (in a group) followed by Tuesdays.

One way I think I could achieve this is by doing a QueryMonday, QueryTuesday...etc so I have dataset or resultsset for each day. Then I could bring into a report, each query as a sub-report. I believe this is overly complex for what i'm trying to achieve. Also, using 5 grouping levels in one report doesn't achieve it either.

View 2 Replies View Related

Total Daily Sales Queries By Model/Total

Mar 8, 2008

Hi,

1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.

2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)

3) I start with daily (Lets don't be too overly ambitious).

4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.

5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?

PS: Please forgive my ignorance :o:(

Thanks (In advance) & God Bless.

View 2 Replies View Related

General :: Report That Show Daily Activities - Adding Time Lengths

Feb 25, 2014

I have a report that shows the daily activities. it shows how long it takes to do each task. however some tasks are 8 hours andsome are 10 hours. when all these times are added together it will give the time on a clock. not a total of hours spent.

i.e.
treatment1 : 8 hours
treatment2: 10 hours

this will give 06:00 in short time. in medium time it will give 18:00.

however if I add another 12 hours to that it will give 06:00.

I want it to show either 1 day and 4 hours /or 30 hours.

View 7 Replies View Related

Queries :: Group And Display Data On Weekly Basis Starting On Sunday

Apr 2, 2013

I'm trying to group and display data on a weekly basis starting on Sunday. I'm using the following in a totals query with "Group By" Week Number: DatePart("ww",[Date])

My problem is that the results are returning some unexpected things.. March seems to have 6 weeks and the query is returning two months containing week number 14.. (as shown below).

2013 March 9
2013 March 10
2013 March 11
2013 March 12
2013 March 13
2013 March 14
2013 April 14

View 4 Replies View Related

Reporting Time Fixed As A Daily Variable

Mar 18, 2012

The previous problem stands solved i.e. ReportTime field shows 9:50 always.But it has created another problem as under

The Formula for ReportTime : Format(Date(),"m/d/yyyy") & " 9:50:00 AM"

The formula for ReportedAt then becomes as ReportedAt: Format(Now(),"hh-nn ampm")

And the next step LateByMinuts:=IIf([StatusID]=1,DateDiff("n",[ReportTime],[ReportedAt]),0)

Where stutusID is

1. Present,
2. AbsentExcused.
3. LeavePrivelege etc.

is correct for the first time.That is when at the first time the Reported time (system time) is recorded it works fantastically but when next time the Form is opened the saved "LateBYMinuts" field is changed with the system date thus disparaging the previous save data.

View 1 Replies View Related

Queries :: Time Sheet / Payroll Database - Calculating Total Daily Hours

Feb 18, 2014

Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.

For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.

The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!

At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).

View 3 Replies View Related

Changing Hours From Daily To Weekly To Accumulate Vacation?

May 21, 2012

I am building an attendance database. The attendance data will be inputted at a daily level. That is working fine and the hours are calculating correctly. The next thing I need to do is create a Table (maybe) that will show the total hours worked in a week. The purpose of this, and the reason I don't want a report, is that the employee will accumulate vacation based upon the numbers of hours worked per week. I will want to be able to show a running total on the vacation, and will eventually create something to allow them to deduct from those vacation hours when they are used.

View 2 Replies View Related

Queries :: Extract Top 5 Vendors By TOTAL Spend By Group

Aug 7, 2015

I am having trouble extracting top 5 vendor with the sumOfSpend (actual Field name is Spend which is dis-aggregated) for each of 5 groups. Each group has thousands of transactions for dozens of vendors. The Table is designed as follow.

Table1:
GroupName : Text
VendorName : Text
Spend : Currency.

View 3 Replies View Related

Calculating Employee Overtime Worked (weekly Total)

Oct 26, 2006

Hello my beautiful worldwide friends :D
I am trying to calculate employee Overtime hours from their recorded TimeIn and TimeOut if over 7.5 hours. So anyday they work past 7.5 hours should be calculated and totalled at the end of the week. And i can't seem to figure it out, maybe my maths is bad? Maybe it's my query? The section of my report? What could i be doing wrong here, i have spent hours and im getting no where.

I have attached my report.
Can you help me figure out why my daily total shows but the weekly total is not showing?

View 9 Replies View Related

Daily Total Query

Sep 7, 2006

Hi,

I have a database, in which I need to add up the total number of entries made at any point of the day. I have started by creating a query with the entry's SerialID (unique) and the date it was entered, however I am stuck already.

Any help would be appreciated,

Ben

View 12 Replies View Related

Use A Query To Do A Running Total & Comparison With Weekly Data Points

Apr 13, 2006

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:

View 3 Replies View Related

Queries :: Transpose Table Columns Into Rows

Jun 18, 2013

Is there a way to create a query that will transpose table columns into rows:

Genes Cytoband
xxxx yyyyyy
xxx yyyyyy
xxx yyyyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyyy
xxxxxxx yyyyy
xxx yyyyyy
xxxx yyyyy
xxxxxxx yyyyyyy
xxxx yyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyy

Is there a way to display the table in a single row separated by commas:

xxxx yyyyy, xxxx yyyyyyy, xxxxxxx yyyyyyyy, xxxxx yyyyyyy

View 11 Replies View Related

Queries :: Transpose And Concatenate Query Results

Jul 24, 2014

I am trying to generate a simple report that can be imported to excel for analysis. Currently the database documents failure causes associated with a repair. I need a "Report" that does not duplicate the failed items in the event there is more than one cause associated with the failure. some repaired items may have up to 3 causes of failure. What I would like to do is have the query show one record instead of three records (for the cases were three causes of failure were identified for a repair)

Current query format

Code:
facilityID Failure Cause
1 Electrical Surge
1 Mechanical Wear
1 Corrosion
2 ......
2 ......
.... ......

Desired query format

Code:
FacilityID Failure Cause(s)
1 Electrical surge, Mechanical wear, Corrosion
2 .....
.... .....

I was reading about a SQL transpose call but I do not need all fields transposed (only the failure causes transposed and concatenated into one field).

Test database attached with a query that shows the data I want to transform into the desired format.

View 6 Replies View Related

Queries :: Transpose Vertical Text Values To Horizontal

May 25, 2013

I have a Volunteer Database that has teams. There is only one team leader but some of the teams have two or three assistants. Currently I have a query that lists all the assistants for each team vertically.What I want to do is list the Assistants horizontally under headings like Assistant 1; Assistant 2; and Assistant 3 for each team on one line.

View 6 Replies View Related

Query : Group By & Sum Total With User Input?

Mar 15, 2007

Hi all.
Stumbled onto this forum during a google search.. looks like a great forum.

I have a question.
this thread http://www.access-programmers.co.uk/forums/showthread.php?t=124689

goes into a simple function to group by the name and add their total.. however what i need to do is that plus have a user input of the date.

eg of the table

Date Title Invoice
02/02/07 ABC123 11.00
02/02/07 ABC123 12.50
02/02/07 DEC123 11.50
03/02/07 ABC123 10.50

What i need is a paramater query to be able to work to give me the total of the groups..
Whenever i try to join my Parameter query with my sum query it gets messed up..

heres the code... what am i doing wrong?..
What i want displayed after they input the date is the group by and sum total of the parameter query..

SELECT RawData_tbl.[Title 2], RawData_tbl.[Estimated Value], RawData_tbl.[Date In], RawData_tbl.Quote, RawData_tbl.GST, Sum(RawData_tbl.Invoice) AS SumOfInvoice
FROM RawData_tbl
GROUP BY RawData_tbl.[Title 2], RawData_tbl.[Estimated Value], RawData_tbl.[Date In], RawData_tbl.Quote, RawData_tbl.GST
HAVING (((RawData_tbl.[Date In]) Between [Please Type the first day of the month] And [ Please insert the last day of the month]))
ORDER BY RawData_tbl.[Title 2];

View 5 Replies View Related

Queries :: Automatically Building Reporting Database?

Sep 13, 2013

I want to build a database stored on a server which can be used by our employees as basis for their Excel pivot reporting

The idea is as follows:

I make a query that will get selected data (via odbc) from several Progress database tables of our ERP system and write them to one MS access table

The idea is to run each night at midnight a query (append or make table) via a schedule and gradually build a huge database table holding sales details

This table will be used as basis for the Excel pivot reporting .These reports are currently run directly on the ERP system's database which slows things down

Question:

1. Is it possible to automate the queries (perhaps via scheduler or 3rd party program ?

2. what would be better to use an append query that adds data or a maketable query which constantly replaces the data ?

3. How many ODBC connections on a MS Access database can I have at the same time ?

View 1 Replies View Related

Queries :: Reporting When Column Header Keeps Changing

Dec 18, 2013

I have a crosstab query that reports KPI numbers for the last 5 weeks, using the week no as the column header. Then I am generating a report from this query. It works 100%

However, when I go to the next week, the report fails as the first week from the previous query no longer exists

So, this is happening...

The first report generated columns called 39,40,41,42,43 because the crosstab query uses week number as the header.

The second report (1 week later) cant find column 39 so it crashes!

Can't see how to change the column header to a generic as it is created by the crosstab.....

View 3 Replies View Related

Queries :: Self-Referencing Running Total Used To Calculate Next Total In A Query

Jul 23, 2015

I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records

I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do

If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..

View 9 Replies View Related

Queries :: Save Weekly Changes To A Table

Nov 18, 2013

We import data to a table on a weekly basis. We delete the table and replace the data. Most of the data is the same. It's date's, group names, ...

Now we would like to save the weekly table and add a saved date. Then we would like a query that puts all the saved tables together and that we could filter.

For example. A team is planned to do a work on 01/01/13. The next week it has changed to 07/01/13. So the data in the table it updated but we can't track the changed date. If we had the different tables we could filter that team and so that the work was originaly planned on date x to be done on the 01/01/13, on date y it was 07/01/13....

Of course if we would save the complete table every time it would mean a lot of useless data that hasn't changed. So a better solution would be that a query could see which data has changed and it only saved those lines on that date.

View 11 Replies View Related

Queries :: Reporting Results Of DLookup In Query For Reports

Jan 7, 2014

I have been all over the internet and trying different things for hours to no solution. I have created a form (Code) which I am using to auto fill two categories in a form using dlookup. Both categories fill perfectly but I cannot get the information to transfer to a query in order to capture the information in a report.

These are the dlookups I've been using and the categories I'm attempting to capture are "Description" and "Category"

=DLookUp("Description","[Code]","[CodeID]='" & [ViolationCombo] & "'")
=DLookUp("Description","[Code]","[CodeID]='" & [ViolationCombo] & "'")

View 3 Replies View Related

Forms :: Total On Report - Count Of Records Found For Each Group

Sep 20, 2014

I have a report which gives me a count of records found for each group

group 1 - 10
group 2 - 13
group 4 - 82

what i want is a total below this - ive looked at calculated controls however cant seem to get it what soever - I've tried likes of =sum([counts])

View 1 Replies View Related

Reports :: Reset Total Pages For Each Group In Access Report

Mar 3, 2013

I can not get my Access Report to give me a total number of pages by group. I've been able to have it give the correct page number per group but not the total number of pages in each group. I've looked at the threads and it appears that in order to get this, you must do a 2 part pass. 1st to get the page number and then to get the total papers per group.

I've used the code supplied and when I do a print preview, I get the message that it can not find the control, Me!ctlGrpPages. This is the control I created and placed in my page footer section of the report. I've also seen numerous references to the report, "Employee Sales by Country" in the Northwind database. I downloaded the database but could not find this report in the database.

View 3 Replies View Related

Queries :: Weekly Query That Update Reports

May 16, 2014

I have made a query that was supposed to update my reports every week with new inquiries that we get.

I thought i had solved this but when i opened the report this morning, it's just showing last week's.

I have this as the criteria:Between (Date()-7-Weekday(Date(),2)+1) And (Date()-Weekday(Date(),2)+1)

It's probably wrong.

View 7 Replies View Related

Forms :: Counting Multiple Checkboxes And Calculate Total Of Whole Group Of Users

Aug 27, 2014

Access 2003. Job booking data base with up to group of 10 different users. At the end of the month, I need to count the total amount each user has checked a particular check box and then automatically calculate the total of the whole group.I have only a basic working knowledge of Access 2003....

View 1 Replies View Related

Queries :: Linking Weekly Rota System To A Date

Oct 4, 2013

I have a staff rota system that works on a rolling 4 weekly basis. I am using a table to store the shifts of a person dependent on week. I want to be able to tell access that Monday on week 1 corresponds to a certain date and then get access to figure out the rolling system based on that date.

eg Monday 21/7/13 is week 1 (7 days later it knows it is linked to week 2)

This is so if a staff member is off sick I can say they were off sick on the 24th and it will populate their timesheet with the corresponding shift without me having to input it manually. Doable?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved