Making one date field out of two 1

[Danglez]

Here is a sample of my query:
Maturity Date|MaturityDateAmount|Value Date|ValueDateAmount
|8/19/2004|($85,260,778.63) |8/12/2004| ($85,260,778.63)
|8/16/2004|($27,004,440.00) |8/12/2004| ($27,004,440.00)
|8/16/2004|($7,301,192.33) |8/12/2004| ($7,301,192.33)
|8/26/2004|($1,000,525.00) |8/12/2004| ($1,000,525.00)
|9/16/2004|$1,000,350.00 |8/16/2004| $1,000,350.00
|8/20/2004|($66,674,054.89) |8/17/2004| $66,666,666.00
|8/30/2004|($951,961.41) |7/28/2004| ($951,961.41)

These dates are for a 30 day projection report so at least one of the date fields is within the next 30 days.

I want to know how to make ONE date field that lists the appropriate amount next to it.

Of particular interest is the 8/20-8/17 record, it would have to be able two list both of those date (since they are both within 30days) with the appropriate amount next to it.

So the end result should look like this:

|Date(within 30d)| Amount(Value or Maturity depending on date)|

Thanks.

 

[hneal98]

You have two differnt date fields and two different value fields. Which of each do you want to display?

 

[Danglez]

I want to display MaturityDateAmount with Maturity Date and ValueDateAmount with Value Date.

Hope this clarifies things

 

[OhioSteve]

Is this the task structure~
1)create a new table with the fields "date1" and "amount1"
2)Evaluate each "maturity date" from the old table
2.1)if the date is in the next 30 days
2.2)insert the date and amount into the new table
3) Evaluate each "value date" from the old table
3.1)If the date is within the next 30 days
3.2) insert the date and amount into the new table

I would perform step 1 in the Access GUI.
I would create an append query to complete step 2.
I would create another append query to complete step 3.

 

[PHV]

Something like this ?
SELECT [Maturity Date] As [Date], MaturityDateAmount As Amount FROM yourQueryName
WHERE [Maturity Date] Between Date() AND Date()+30
UNION ALL
SELECT [Value Date], ValueDateAmount FROM yourQueryName
WHERE [Value Date] Between Date() AND Date()+30
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

When the poster said "...make one date field..." I interpreted the word "make" literally. I thought that we had to make a table. Your union query would work great if they just wanted a recordset.

 

[PHV]

And we can't use a saved union query as source for a create table query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Danglez]

PHV I took your advice and had the maturity query make the table, used the value query to append to the new table. Then I made a query to sort and sum the new table by date. I then created a macro to automate the whole process. That's exactly what I needed and it works perfectly, thank you very much!