Solved

Combine dates from two fields

Posted on 2012-04-11
14
604 Views
Last Modified: 2012-04-15
I have a query in access 2007, one field in one row has an ID number (i.e. 11476) and another field in a second row has an ID number (i.e. 11476b). Each row has two date fields, a beginning and ending date. The first row beginning date is 6/1/11 and the first row ending date is 1/31/12. The second row beginning date is 2/1/12 and the second row ending date is 5/31/12. The beginning date field is named BPB and the ending date field is named BPE. How do I combine the dates, so the result will be 6/1/11-5/31/12 using sql?
0
Comment
Question by:Barbara69
  • 7
  • 6
14 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37834655
SELECT [BPB] &"-" & [BPE] AS CombinedDates
FROM YourTable
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37834712
Barbara69,

I have seen so many question like this with sample data that turns out to be the incosistent..

so better if you can upload a copy of the db with the table..
0
 

Author Comment

by:Barbara69
ID: 37834762
I understand what you suggested, but that's not what I'm looking to do. I want to take the beginning date of one row and combine it with the ending date of another row. So, how do I specify which begin and end date to combine from each row?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37834773
you can try this query, assuming the name of the table is TableA

SELECT Val([ID Number]) AS ID, (select min(t.bpb) from tablea as t where  val([t].[id number])= val([tableA].[id number])) & " - " & (select max(t.bpe) from tablea as t where  val([t].[id number])= val([tableA].[id number])) AS FromToDate
FROM TableA
GROUP BY Val([ID Number]);
0
 

Author Comment

by:Barbara69
ID: 37834874
I want to take the Budget Period Begin Date of 10280 and combine it with the Budget Period End Date of 10280b to make the date range of 6/1/10-5/31/11.


Grant Number      Budget Period Begin Date      Budget Period End Date
10280              6/1/2010                            12/31/2010
10280b              1/1/2011                             5/31/2011


Please see attached.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37834884
where is the attached ?

the query i posted will do it for you, just change the field names accordingly
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37834907
SELECT Val([Grant Number]) AS GrantID, (select min(t.[Budget Period Begin Date]) from tblGrant as t where  val([t].[Grant Number])= val([tblGrant].[Grant Number])) & " - " & (select max(t.[Budget Period End Date]) from tblGrant as t where  val([t].[Grant Number])= val([tblGrant].[Grant Number])) AS FromToDate
FROM tblGrant
GROUP BY Val([Grant Number]);
0
 

Author Comment

by:Barbara69
ID: 37835971
Capricorn1,

I've attached a copy of a qry I ran showing an expended amout column that I exported to Excel. Would the same sql you provided work for adding together the amounts from the expended column from two different rows?

Why are you using Val[Grant Number]), since Grant Number is already a number? How would I get only those grant numbers > 10125? I added HAVING (((Val[txtGrantNumber]))> "10125"));, but it doesn't work. I tried HAVING (((Val([txtGrantNumber])) between "10125" and "12577")), it sort of worked except for all the numbers before 10125. So, how do I get rid of all the numbers before 10125? See below.

Grant Number      FromToDate
11      8/1/2011 - 6/30/2012
116      6/1/2000 - 5/31/2001
117      6/1/2000 - 5/31/2001
118      1/12/2001 - 8/31/2001
119      10/2/2000 - 5/31/2001
120      10/1/2000 - 5/1/2001
121      8/1/2000 - 6/30/2001
122      10/1/2000 - 5/31/2001
124      12/1/2000 - 4/30/2002
1113      9/1/2001 - 8/31/2002
1114      7/1/2001 - 5/31/2002
1115      7/1/2001 - 5/31/2002
1116      7/1/2001 - 5/31/2002
1117      7/1/2001 - 5/31/2002
1118      7/1/2001 - 5/31/2002
1119      7/1/2001 - 7/31/2002
1120      7/1/2001 - 5/31/2002
1121      7/1/2001 - 6/30/2002
1122      7/1/2001 - 5/31/2002
1123      7/2/2001 - 5/31/2002
1124      7/15/2001 - 5/31/2002
1125      8/15/2001 - 5/31/2002
1126      8/15/2001 - 7/31/2002
1127      9/15/2001 - 5/31/2002
1128      9/15/2001 - 8/31/2002
1129      9/1/2001 - 8/31/2002
1130      10/1/2001 - 9/30/2002
1205      9/1/2001 - 8/31/2002
1206      9/1/2001 - 3/31/2003
1207      6/1/2001 - 5/31/2002
1208      6/1/2001 - 5/31/2002
1209      7/1/2001 - 6/30/2002
1210      6/1/2001 - 5/31/2002
1211      6/1/2001 - 8/31/2002
10125      9/1/2010 - 8/31/2011
10126      4/1/2011 - 4/30/2012
10130      1/1/2011 - 12/31/2011
10131      1/1/2011 - 12/31/2011
10132      2/1/2011 - 3/31/2012
10140      2/1/2011 - 2/29/2012
10168      4/1/2011 - 4/30/2012
10275      6/1/2010 - 5/31/2011
10280      6/1/2010 - 5/31/2011
10375      10/1/2010 - 9/30/2011
10376      6/1/2010 - 5/31/2011
10377      6/1/2010 - 5/31/2011
10379      6/1/2010 - 8/31/2011
10381      6/1/2010 - 8/31/2011
10382      6/1/2010 - 8/31/2011
10383      6/1/2010 - 8/31/2011
10384      6/1/2010 - 10/31/2011
10385      6/1/2010 - 8/31/2011
10386      7/1/2010 - 6/30/2011
10387      6/1/2010 - 8/31/2011
10388      10/1/2010 - 10/31/2011
10464      9/1/2010 - 9/15/2011
10469      6/1/2010 - 5/31/2011
10573      7/1/2010 - 6/30/2011
10574      6/1/2010 - 5/31/2011
10576      7/1/2010 - 6/30/2011
10577      9/1/2010 - 8/31/2011
10654      11/1/2010 - 10/31/2011
11132      8/1/2011 - 7/31/2012
11134      10/1/2011 - 9/30/2012
11135      10/1/2011 - 9/30/2012
11136      6/1/2012 - 5/31/2013
11137      10/1/2011 - 9/30/2012
11138      10/1/2011 - 9/30/2012
11139      12/1/2011 - 11/30/2012
11140      10/1/2011 - 9/30/2012
11141      3/1/2012 - 2/28/2013
11142      6/1/2012 - 5/31/2013
11143      2/1/2012 - 1/31/2013
11144      1/1/2012 - 12/31/2012
11145      3/1/2012 - 2/28/2013
11146      1/1/2012 - 12/31/2012
11147      2/1/2012 - 1/31/2013
11148      2/1/2012 - 1/31/2013
11149      1/1/2012 - 12/31/2012
11150      3/1/2012 - 2/28/2013
11151      2/1/2012 - 1/31/2013
11152      4/1/2012 - 3/31/2013
11153      4/1/2012 - 3/31/2013
11154      3/1/2012 - 2/28/2013
11155      4/1/2012 - 3/31/2013
11156      1/1/2012 - 12/31/2012
11157      1/1/2012 - 12/31/2012
11158      1/2/2012 - 8/31/2012
11226      5/1/2012 - 4/30/2013
11240      3/1/2012 - 2/28/2013
11380      6/1/2011 - 5/31/2012
11468      6/1/2011 - 8/31/2012
11475      10/1/2011 - 9/30/2012
11476      6/1/2011 - 5/31/2012
11477      6/1/2011 - 5/31/2012
11486      7/1/2011 - 6/30/2012
11564      9/16/2011 - 8/31/2012
11569      6/1/2011 - 5/31/2012
11754      11/1/2011 - 10/31/2012
12375      3/1/2012 - 2/28/2013
12576      6/1/2012 - 5/31/2013
Vendor.accdb
qryAwardedAmt.xlsx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37837038
<Why are you using Val[Grant Number]), since Grant Number is already a number?>

                 have you seen my post at http:#a37834712 ?

your initial posting indicates the ID numbers as (i.e. 11476) and another field in a second row has an ID number (i.e. 11476b)

11476 is not the same as  11476b
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37837075
change this

HAVING (((Val([tblVendor.txtGrantNumber])) Between "10125" And "12577")

with


HAVING (((Val([tblVendor.txtGrantNumber])) Between 10125 And 12577)    '< remove the quotes

the Val() function converts the text number data type to  Number
0
 

Author Comment

by:Barbara69
ID: 37839218
I'm having trouble converting your sql statement for my actual database. The field names are txtGrantNumber, dteBudgetBeginDate and dteBudgetEndDate from tblAward Data.
0
 

Author Comment

by:Barbara69
ID: 37847275
I got the qry working, but I don't know how to pick up the two expended amounts associated with each of the GrantID numbers. So, I need to add the amount that goes with 11476 to the amount that goes with 11476b to get a total amount expended.
0
 

Author Comment

by:Barbara69
ID: 37847569
Just so you understand what I'm talking about:

Grant ID     Pymt1  Pymt2  Pymt3    Expended
11476           100       50       60           210
11476b           80       90     100           270

I need total expended for 11476 expended amount + 11476b expended amount = 480.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37847657
Barbara69,

just making it clear, youask in your original post

<How do I combine the dates, so the result will be 6/1/11-5/31/12 using sql?>

i believe this is already been resolved..

we can't go on resolving all your problems one at a time in a single thread.
so, please post another question regarding your other problem.

please follow the guideline of one question per thread..
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question