Eh, don't know, "A real challenge" maybe

[Guest]

I have a really cumbersome problem, if someone can solve this one you really
should get the Nobel Prize…

I have a table that has 3 fields containing dates. Let’s call them “Dateâ€,
“Date-1†and “Date+1â€, i.e. in “date†there is e.g. 2005-06-02, then the date
in “date-1†is 2005-06-01†and so on i.e. there can never be the same date in
the same row. The table also includes other text fields.

Now I would like to have a table that summarizes these three fields so I get
one field that sort the dates chronologically like:

New Field 1 New Field 2

2005-06-01 5
2005-06-02 8
…

In another field I would like to have the number of “hits†(as in new field
2 above) this specific date gave when searching within all the three fields
as mentioned in the beginning. I.e. no matter if the date (e.g. 2005-06-02)
is in the “dateâ€, “date-1†or “Date+1†field I want it to be grouped in to
the row for 2005-06-02 and counted. This means that each row from the
beginning (that contains 3 different dates) must be included in three
different rows in the new table because the row with, e.g. 2005-06-02 in the
“date†field will be included in the new rows for 2005-06-01, 2005-06-02 and
2005-06-03.

Does anyone understand what I want to do and does anyone know how to do it?

Can I give you some more information to help you understand?

Andreas

 

[Douglas J. Steele]

Why are there 3 dates in a single row? If Date-1 is always one less than
Date (and Date+1 is always one more than Date?), only Date should be stored
in the table, and you should create a query that calculates Date-1 and
Date+1, then use that query wherever you would otherwise have used the
table. (I assume you know Date is not an appropriate name for a field: it's
a reserved word, and you'll eventually run into problems if you use reserved
words for your own purposes.)

If I understand you correctly, if you have 2 rows in your table:

Date Date-1 Date+1
2005-06-02 2005-06-01 2005-06-03
2005-06-03 2005-06-02 2005-06-04

then you want:

NewField1 NewField2
2005-06-01 1
2005-06-02 2
2005-06-03 2
2005-06-04 1

Following up on my suggestion about that you only store Date in the table,
and use calculated fields to get Date-1 and Date+1, you can do the same
thing with a Union query:

SELECT DateField, Field1, Field2 FROM MyTable
UNION ALL
SELECT DateAdd("d", -1, DateField), Field1, Field2 FROM MyTable
UNION ALL
SELECT DateAdd("d", 1, DateField), Field1, Field2 FROM MyTable

The UNION ALL is important: if you only use UNION, duplicate rows will be
thrown away.

With your data presented in that format, it's pretty easy to get what you
want. Assuming you stored the UNION query as, say, qryUnion, you need a
query:

SELECT DateField AS NewField1, Count(*) AS NewField2
FROM qryUnion
GROUP BY DateField

In fact, if you're using Access 2000 or newer, you can combine everything
into one query:

SELECT DateField AS NewField1, Count(*) AS NewField2
FROM
(
SELECT DateField, Field1, Field2 FROM MyTable
UNION ALL
SELECT DateAdd("d", -1, DateField), Field1, Field2 FROM MyTable
UNION ALL
SELECT DateAdd("d", 1, DateField), Field1, Field2 FROM MyTable
)
GROUP BY DateField

 

[Guest]

What you could do is have your three text boxes; txtToday, txtTomorrow and
txtYesterday, when you load your form have it load the relevant dates into
your text boxes,

txtToday = Datevalue(now())
txtTomorrow = Datevalue(now()+1)
txtYesterday = Datevalue(now()-1)

Then the code to retrieve your 'New Field 2' values:
txtTodaysCount = DLookup("New Field 2", "tbl_Table", "[txtToday]=" &
Format(Date, "yyyy" & "/" & "mm" & "/" & "dd"))

txtToomorrowsCount = DLookup("New Field 2", "tbl_Table", "[txtTomorrow]=" &
Format(Date, "yyyy" & "/" & "mm" & "/" & "dd"))

txtYesterdaysCount = DLookup("New Field 2", "tbl_Table", "[txtYesterday]=" &
Format(Date, "yyyy" & "/" & "mm" & "/" & "dd"))

Something like that should work

 

[Guest]

Thanks Mr Steele

Yes, you caught me there, I could not find a solution as you gave me so I
created three fields with dates. Your solution is working good , but in this
query I also want to have four more fields from the table, how do I add them,
I have not managed to find out, I get error messages.


Thanks
Andreas

"Douglas J. Steele" skrev:

 

[Douglas J. Steele]

In which query? The Union query?

Make sure you add the fields to all 3 of the subqueries:

SELECT DateField, Field1, Field2, Field3, Field4 FROM MyTable
UNION ALL
SELECT DateAdd("d", -1, DateField), Field1, Field2, Field3, Field4 FROM
MyTable
UNION ALL
SELECT DateAdd("d", 1, DateField), Field1, Field2, Field3, Field4 FROM
MyTable

Or to the grouping query? Make sure all fields are in the GROUP BY clause:

SELECT DateField AS NewField1, Field1, Field2, Count(*) AS NewField2
FROM qryUnion
GROUP BY DateField, Field1, Field2