Grouping Columns With Dates?
Apr 8, 2012
I am working on a very large table that i have to group by Min & Max Dates.
ColumnA ColumnB
1-Jan Melbourne
2-Jan Melbourne
3-Jan Melbourne
4-Jan Melbourne
5-Jan Sydney
6-Jan Sydney
7-Jan Sydney
8-Jan Sydney
9-Jan Sydney
10-Jan Sydney
11-Jan Sydney
12-Jan Melbourne
13-Jan Melbourne
14-Jan Melbourne
15-Jan Melbourne
I want the above table to provide an output as below:
Min Max City
1-Jan 4-Jan Melbourne
5-Jan 11-Jan Sydney
12-Jan 15-Jan Melbourne
i have done this in excel by sorting dates and then comparing values in Column B.
View Replies
ADVERTISEMENT
Jul 21, 2005
I am trying to display five columns from three seperate tables. I thought the GROUP BY operator would fix this, but I have more than one column to select. The query is fine but I have redundant fields showing. Oh, I'm not worried about the calculation "[employee.salary]*0.1 AS Salary_After_Raise." I just want to concentrate on narrowing the fields down first.
SELECT ID, FirstName, LastName, Salary, [employee.salary]*0.1 AS Salary_After_Raise, City
FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee
WHERE worksat.store = store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;
View 1 Replies
View Related
Dec 5, 2007
I have groups that run horizontally down my report. Is there a way to group items vertically on the report based on the horizontal information.
For instance I have a client list with the total of the transactions they made. Then I would like to run in the same row the breakdown per quarter showing all 4 quarters in the same row as the client name, but separated into columns for each quarter.
Any help would be greatly appreciated
Thank you!
View 1 Replies
View Related
Aug 1, 2006
I have a query which has many sums and counts on things like "Company name", "region name" and "Development Name".
I'm using this query for a report to do lots of percentages with, but now i need to filter this also by a date period.
So the user choses "Alex Homes" as the company name and then "July 2006" as the reporting month, and i need all the sums and counts to stay the same and only count/sum the records in the chosen month.
I can't seem to think of a way to do this.
If you need more info then ask.
View 4 Replies
View Related
May 8, 2005
Hello, I am new to Access and I am having a problem with something. I have a query which returns, among other things, dates. I need to organize (group) the dates into weeks. Does Access have a function which could do this? Or some other way of grouping the dates? Or How would I got about writing a function in Access?
This is done in a Pivot Table right now, and I just use a calculated field which is the date / 7. This gives some god awful numbers (like 5944), and not anything like what I want. I would want the function to return something like,
"Feb. 5, 2005 - Feb. 12, 2005". Any suggestions?
View 2 Replies
View Related
Sep 12, 2013
I have one table in my access, and out of that I need to show a queri that will show only following result in 2 columns ID (same as original table)
SCHOOL_YEAR (all the students that are):
1 year and 1 year (repeat) as 1 year
2 year and 1 year (repeat) as 2 year
3 year and 1 year (repeat) as 3 year
Not to show dripouts, na and finished
Original table:
IDNAME
11 year
42 year
53 year
10dropout
11finished
12n/anot categorized
131 year (repeat)
142 year (repeat)
153 year (repeat)
View 3 Replies
View Related
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
Apr 5, 2014
I deal with different blood test reports for same patient on different dates. Because tests are so many, so i have arranged them on different pages of the same main report. But the problem is, that access shows different records of the same tests for different dates one under the other as a set...while i want it to show in parallel columns with test label on left side and column heads as different dates.
View 3 Replies
View Related
May 19, 2015
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
View 14 Replies
View Related
Aug 8, 2013
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
View 2 Replies
View Related
May 14, 2014
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
View 2 Replies
View Related
Oct 30, 2007
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
View 4 Replies
View Related
Mar 22, 2007
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
View 3 Replies
View Related
May 12, 2014
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
View 1 Replies
View Related
Aug 28, 2013
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies
View Related
Apr 9, 2015
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
View 4 Replies
View Related
Sep 7, 2006
Hiya-
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
Many thanks, Jules.
View 3 Replies
View Related
Jul 8, 2014
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
View 2 Replies
View Related
Nov 15, 2011
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
View 10 Replies
View Related
May 2, 2006
I have created an invoicing system for a CD shop
There is a transaction table which has each individual cd sold as a seperate transaction. Each transaction has an order number, so there can be more then one cd sold per order, but they all still have their own record in the table.
im now invoicing each order by mail merge based on a query that finds all the details on every transaction but wht ive found is that the mail merge puts each transaction onto a different page even if its from the same order number as another.
does anyone know how to group each order in the query so that all the items in one order come in a single invoice?
View 3 Replies
View Related
Jun 21, 2005
Hi, I have a query which numerous fields, and I need to make a report based on the query. However I need to group 3 fields in the query and press the sum button on 2 of them, Qty and Total Price (which is qty*price)
I need to do this so in the report when a particular wine is purchased more than once, instead of repeating the peoples name who bought the wine, it will only show 1 and automatically add the rest to the total price.
I dont know how to group within a query, can someome please tell me how? Thanks.
View 1 Replies
View Related
Jul 6, 2005
Hi to all;
I have one code of 6 digits; each digit refer to a group; first 2= product group; beverage; live animals,….itc (24 product groups), the 3ed digits= food product, the 4th-6th digit= detail product group; vegetables, fruits,….,the rest of digits refers to product name, carrot, apple,…itc , example 070511
How can I use the query to sum the product value at different group level; example the first 2= product group, ..?
Do I have to split my code to 6 cods to do my calculation?
Thank in advance for help
majed
View 1 Replies
View Related
May 2, 2006
I have created an invoicing system for a CD shop
There is a transaction table which has each individual cd sold as a seperate transaction. Each transaction has an order number, so there can be more then one cd sold per order, but they all still have their own record in the table.
im now invoicing each order by mail merge based on a query that finds all the details on every transaction but wht ive found is that the mail merge puts each transaction onto a different page even if its from the same order number as another.
does anyone know how to group each order in the query so that all the items in one order come in a single invoice?
View 7 Replies
View Related
Sep 21, 2004
Hi all
I am trying to find a way of finding the number of a group of sessions as a percentage figure. e.g. total number of clients attending 1-3 sessions = 20%, 4-6 =15%, 7-12 =21% 1-2years = 8%, etc. and entering this calculation in a report.hope you can help.
Dave
View 9 Replies
View Related
Jul 18, 2005
Question on grouping within Access Reports:
A simplistic view of the report I'm trying to generate is as such:
Company Name - Company Description - Employee
I am grouping by Company name, and I am hiding duplicates of Company Description because they can be long. I also have the Company Description as Allowing to Grow.
The problem is that the first record gives the company, the description, and the employee name on the first line just fine, but the second employee name won't be listed until the Company description ends. When the description is about a paragraph long, the other employees look very seperated from the initial record.
Any way to fix this?
View 1 Replies
View Related
Mar 9, 2008
Hi,
can form objects be grouped? i currently have numerous buttons on a form that are shown according to a button selection. my current code makes all buttons visible / invisible singularly but i wiondered if they could be grouped/ named and the get the code to make the group visible / invisible?
many thanks,
NS
View 4 Replies
View Related
