?
Solved

Combine dates from two fields

Posted on 2012-04-11
14
Medium Priority
?
622 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 2000 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 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