Combine dates from two fields

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?
Barbara69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
SELECT [BPB] &"-" & [BPE] AS CombinedDates
FROM YourTable
0
Rey Obrero (Capricorn1)Commented:
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
Barbara69Author Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
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
Barbara69Author Commented:
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
Rey Obrero (Capricorn1)Commented:
where is the attached ?

the query i posted will do it for you, just change the field names accordingly
0
Rey Obrero (Capricorn1)Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Barbara69Author Commented:
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
Rey Obrero (Capricorn1)Commented:
<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
Rey Obrero (Capricorn1)Commented:
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
Barbara69Author Commented:
I'm having trouble converting your sql statement for my actual database. The field names are txtGrantNumber, dteBudgetBeginDate and dteBudgetEndDate from tblAward Data.
0
Barbara69Author Commented:
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
Barbara69Author Commented:
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
Rey Obrero (Capricorn1)Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.