Queries :: Grouping Data By Week And Display

Mar 19, 2013

I am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.

Using the following in the query design window.

Week of Year: Format([Date],"ww")

This is returning:
1
10
11
12
2
3
4
and so on.. See the problem?

I tried sorting but that doesn't work.

I also tried the following:

Format([Date],"mmmm,ww")

But this is returning
December,51
December,52
February,8
February,9
January,1
January,2
January,3

Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.

View Replies


ADVERTISEMENT

Grouping Daily Data By Week

Nov 2, 2005

Hi all,

I currently have a table that holds 2 months of data. Let's just use January/February 2004 as an example.

The field name is [DateReceived] and goes from '1/1/2004' to '2/29/2004'. I need to group my records in 7-day spans for a future query, so anything from 1/1 to 1/7 would be grouped, etc.....

I have no idea how to do this. Any help would be great.

sanctos

View 2 Replies View Related

Queries :: Extract Day Of Week From A Date To Link To Data

Dec 8, 2014

I want to use the expression Date() in a query to get today's date and then format this to "ddd" to give me the day of the week.

I then want to link this "ddd" to data in my table that contains the three letter days of the week.

How do I make the day become "static" in order to be able to link.

View 6 Replies View Related

Queries :: Pull Data Values In Array To Become Grouping Headers In A Report

Apr 23, 2014

I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.

My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.

Is it possible to write a query from this table that will enable this?

View 4 Replies View Related

Queries :: Making Bird Count Database - Data Recorded On Monthly Basis Week Wise

Feb 21, 2014

I've been requested to make a bird count database.

The bird count data has been recorded on monthly papers like this:

[bird name] [week 1][week 2][week 3][week4]

The number of birds sighted for a given week is written in the [week x] columns.

What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].

So for example:

Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2

highest: =6

View 5 Replies View Related

Display All With DOB Within A Week

Oct 2, 2006

Hi, I am doing a project at school and need to run a query that displays everyone that has a date of birth within 7 days using a date in the format 01/01/2001. I really have no idea how to do this and would really appreciate any help.
Thank you very much
Kris

View 2 Replies View Related

Display Only Last Week's Sales

Jan 19, 2006

Hi all,

I dont know much about access forms - but I have a table that contains all of the members on my site, and the date they joined. Each member can be seen as a sale on my product, so at the moment Im using a simple graph (made using a wizard - shame on me) that just drags out the DateJoined values from the members table.

The result is a bar chart indicating how many sales I made on each day.

Its getting a bit big, and I want to know how I can get it to only drag out last week's sales. I think it will have something to do with the 'Row Source' property of the graph. At the moment its value is...
Code:SELECT (Format([DateJoined],"DDDDD")),Count(*) AS [Count] FROM [tblMembers] GROUP BY (Int([DateJoined])),(Format([DateJoined],"DDDDD"));

Thanks all!

View 2 Replies View Related

Display Date And Week Number??

Apr 10, 2008

hi

i would like to display the date and week number on one of my forms but have no idea about dates and how to code this.
can some one help? id appreciate it it beyond me :confused:

thanks :)

rob

View 4 Replies View Related

Query Criteria To Display Current Week Only?

Jan 23, 2012

I have a database that lists jobs that need doing, in the jobs table there is a Deadline field, this is a Date Time field that specifies when a job must be finished by.

So I'm trying to make a query, using my limited knowledge, that displays all jobs that need finishing between todays date and the rest of the week.

So I tried this and got nowhere:

Between #(Date())# AND #(Date()+7)#

View 4 Replies View Related

Queries :: How To Retrieve Only Numeric Data From F1 And Display That Data In A Field

Oct 1, 2013

Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.

Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?

View 3 Replies View Related

Tables :: Calculated Field In Table - Display Week Number

Jan 13, 2014

I am trying to create a calculated field in my table, I am trying to use the following code:

DatePart("ww",[Gas]![Date Opened])

I receive the following error message: "The expression DatePart("ww",[Gas]![Date Opened]) cannot be used in a calculated column."

Access 2007 - I really need to be able to display the week number in the table, based on the Date Opened field.

View 6 Replies View Related

How To Display Data From 3 Queries In A Form?

Aug 10, 2005

Hi all,

Wondering if anyone can help here? I am currently working on a form that needs to display data from 3 unique queries - a crosstab query and two standard queries (one has line by line data and another summary data) that do not have a common link.

What is the best way to do this? Would it be possible to display the results of each of these queries in a single form by creating 2 subforms from 2 of the queries and placing them in the form of the 3rd?

Any suggestions on how to combine the data from the 3 queries into the 1 form would be much appreciated!!!

Thanks in advance.

G.

View 1 Replies View Related

Queries :: How To Get Query To Display 0 If There Is No Data

Jan 15, 2014

I have a table where I record attendance. In the table they select the type of training in via a check box "Training, Drill or Other". I created a query to show how many times someone has attended the Drills in each quarter (counts the check box). I am wanting to be able to specify which year is displayed as atm it is showing all te quarters for years. The SQL is below.

From this I have a report where it displays the data and have conditional formatted it so that if they have not attended more than 4 times in one quarter the "cell" turns red - the only problem is is that some of the cells have no data in it. How can I get the query to display 0 if there is no data?

QUERY SQL:
TRANSFORM Sum(Abs([ATTENDANCE 2011].DRILL)) AS SumOfDRILL
SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL]
FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID = [ATTENDANCE 2011].ATTENDED.Value
GROUP BY [MEMBERS LIST].[NUMBER/NAME]
PIVOT "Qtr " & Format([DATE],"yyyy/q");

View 1 Replies View Related

Queries :: Display Data From 2 Different Tables In One Form

Apr 21, 2013

I currently have two different excel spreadsheets linked to access 2003. The first few columns are very similar with similar headings. What i have now is a form displaying the data from one spread sheet (Engine Status), and another form displaying the data from the other (Engine Health).

As I am very new to access is there a way that i can link the two spreadsheets together and display all the data on one single form. I would like a combo box to find the required serial number (tail number) and once the serial number is selected the data from both spreadsheets will be displayed.

The spreadsheets i have to work with are of set standard for ease to input data from numerous other sources. I have attached examples of the two spreadsheets with the headings that are the same between the two although i have attached these to a single work book in reality they are seperate work books.

What i would like is for a combo box to display the Tail number then have single boxes to display core serial numbers across the form in order of position, then all the other data associated with that core serial number displayed in seperate boxes below the core serial number. I hope this is possible as it will prevent a lot of toggling between forms.

View 14 Replies View Related

Queries :: 3 Tables Display Data From 1 Depending On Value In Another

May 12, 2015

I'm building a database to record which books Pupils at my school have read.I have 3 Tables:

Pupils - ID, Forename, Surname, ID
Books - BookID, BookTitle, Author, Level, Genre
Read - ID, BookID, DateOut

I'd like to create a query that shows the BookTitle of the latest DateOut.

View 9 Replies View Related

Queries :: Data To Display In Matrix Format

Feb 5, 2014

I am trying to get this data to display in a matrix format.How can I get it to display multiple entries in there, such as Fiona and Chloe in the FR box?

View 14 Replies View Related

Queries :: Display Empty Rows For Missing Data

Jul 28, 2014

I'm trying to make a sub form that displays the hours of an employee selected in a listbox. I've got most of it working but having a bit of an issue.

The info for thre query is in 3 tables:
tblStaff (name etc)
tblShifts (start and end times for days that this employee works)
tblDays (a list of days names so I can use numbers elsewhere)

My query looks like this:

Code:
SELECT tblDays.dayName, IIf(Nz([startTime],"")="","NWD",[startTime]) AS start, IIf(Nz([endTime],"")="","NWD",[endTime]) AS [end], tblStaff.staffName
FROM tblDays LEFT JOIN (tblStaff RIGHT JOIN tblShifts ON tblStaff.staffPK = tblShifts.staffFK) ON tblDays.dayPK = tblShifts.workingDay
WHERE (((tblStaff.staffName)=[Forms]![frmMain]![lst_myTeam] Or (tblStaff.staffName) Is Null));

This worked fine with a single user and some test data - it correctly displayed all days of the week, with start/end times on Mon and Tues where I had entered shift information, and "NWD" against all other days.

However, when I add a couple more employees to the mix it shows the correct info for the first employee, but anybody else it will only display days where person 1 doesn't have any hours. I haven't entered any hours for the new employees, but the query should still display Mon-Sun with NWD in every column. It shows Wed-Sun but Mon and Tues are missing.

I've tried different join types but they all come back with "ambiguous joins" error when I try to run.

View 4 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

Grouping Data

May 16, 2006

How do i group the following records

ID Surname Pack
29679Minican 1
29679Minican 2
27818Oliver 1
27818Oliver 2
27818Oliver 3

so its will show ...

27818Oliver
29679Minican

... on a spreadsheet

View 1 Replies View Related

Grouping Of Data

Jul 6, 2006

I have a table which stores meeting room booking information in half hour slots.
If someone books a 2 hour meeting then 4 records would be produced – one for each half hour. I want to produce a query which will group the data by room (ScheduleID) showing the max and min times (ie the initial start time and final end time) for each event and the event details. The table structure is as follows

ScheduleDetailsID, ScheduleID, CustomerID, ScheduleStartTime, ScheduleEndTime, meeting purpose

And the query I have tried is as follows:

SELECT [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, Min([Schedule Details].ScheduleStartTime) AS MinOfScheduleStartTime, Max([Schedule Details].ScheduleEndTime) AS MaxOfScheduleEndTime, [Schedule Details].[meeting purpose]
FROM [Schedule Details]
GROUP BY [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, [Schedule Details].[meeting purpose];

Can anyone tell me where I have gone wrong. It does not group the data as I want it to ie by room, then time, with only the initial start and final end times. Thanks in advance. Peter

View 3 Replies View Related

Data Grouping

Jan 14, 2008

Hi, Last piece of advice i got from here was excellent so thought i would try it again,

I have records that show delivery days and postcodes some post codes have more than 1 item going to them on several days through the week i was wondering if i could group the same postcodes together so it only showed 1 record instead of a possible 15 but only those delivered on the same day, Is this possible?

View 3 Replies View Related

Reports :: Inserting Page Break To Show Week By Week Pages

Feb 11, 2015

I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.

The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.

I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.

View 8 Replies View Related

Grouping: Data Access Pages

Dec 18, 2007

G'day,

Putting together some data access pages to display data from tables.

All is good except a property that I do not like, when there are multiple records in the returned recordset from the query to the page, I have grouped these by ID (for example).

Each time you move to next record the display contracts back to just the top level of display, that is with the the ID number displayed in a text box and a "+" to expand it out to see the details of the record under that group.

I would like have it always expanded to show the details of the records as I scroll though the ID numbers (the top level of grouping), rather than having to expand for each ID number to see the details.

Not sure that is clear, but any ideas..........

View 1 Replies View Related

Grouping Two Tables Which Have Different Number Of Data

Sep 23, 2007

Hi All,
I have two tables

Table A

EmpID EmpName
001 Will
002 Jack
003 Emma
004 Greg

Table B
EmpName EmpHobby
Will Sport
Jack Reading

I wanna join the two table by using query so the result will be

EmpID EmpName EmpHobby
001 Will Sport
002 Jack Reading
003 Emma (blank)
004 Greg (blank)

what query should I write

Kindly need your help,i am frustrating with this

View 2 Replies View Related

DLookUp, Dates And Grouping Data

Jun 10, 2007

Hello!

Is it possible to group data in form from table within two dates with aggregate function DLookUp,

Thanx

View 1 Replies View Related

Forms :: Grouping Data Within Combo Box

Aug 15, 2013

I have a list of customers, with Account Numbers and Company Names. Now I have entered a combo box in my form control with a list of Account Numbers, how ever I wish to group these Account Numbers because some are the same, with the same company but a different Employee.

So how do I group Account Numbers within the combo box?

View 4 Replies View Related







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