Show earliest date on a report

[ielmrani via AccessMonster.com]

Hi,
I already tried a query to do this but did not work.

I have this form showing 5 text boxes for Status Date and 5 text boxes for
Note like this

Statusdate 11/12/2000
Note XXXXXXxxxxxx

Statusdate1 10/12/2002
Note1 YYYYYY

Statusdate2 7/12/2004
Note1 MMMMMMM

Statusdate3
Note3

Statusdate4
Note5

the user fill in the date and note . in the above example no note or date of
statusdate3 and 4

What I am trying to do is show only the lastest date and note in a report.
in this case 7/12/2004 and MMMMMMM will show on the report.

If statusdate3 and note3 have data that's what I will be showing in the
report. Any one has an idea.

Please let me know if this is not clear.
Thanks in advance.

 

[Klatuu]

Are all the status date field in the same record?

 

[ielmrani via AccessMonster.com]

No they're seperate text boxes. Thanks

Are all the status date field in the same record?

Hi,
I already tried a query to do this but did not work.

[quoted text clipped - 28 lines]

Please let me know if this is not clear.
Thanks in advance.

 

[Klatuu]

Sorry, that is not the answer to the question, but I think it answers the
question.
The question is, where is the actual data? TextBox controls only display
data, the do not contain it.
--
Dave Hargis, Microsoft Access MVP

No they're seperate text boxes. Thanks

Are all the status date field in the same record?

Hi,
I already tried a query to do this but did not work.

[quoted text clipped - 28 lines]

Please let me know if this is not clear.
Thanks in advance.

 

[John W. Vinson]

I have this form showing 5 text boxes for Status Date and 5 text boxes for
Note like this

What's the structure of your Tables?

Tables are fundamental, and store data.
Forms are secondary, they're just tools to display data stored in Tables.

If you're starting your design with the Form you're on the wrong track!

 

[ielmrani via AccessMonster.com]

Sorry about the confusion. The data is of course stored in a table like this:


StatusDate note Statusdate1 note1 statusdate2 note2
etc

 

[ielmrani via AccessMonster.com]

Statusdate Note Statusdate1 Note1
statusdate2 Note2
11/12/2000 XXXXXXxxxxxx 10/12/2002 YYYYYY

etc...






Statusdate2 7/12/2004
Note1 MMMMMMM

Sorry about the confusion. The data is of course stored in a table like this:

StatusDate note Statusdate1 note1 note2
etc
[quoted text clipped - 5 lines]

If you're starting your design with the Form you're on the wrong track!

 

[John W. Vinson]

Sorry about the confusion. The data is of course stored in a table like this:


StatusDate note Statusdate1 note1 statusdate2 note2
etc

In that case the structure of your table is incorrect.

If each item can have multple notes and multiple status dates, then the proper
structure would have *TWO* tables in a one to many relationship; the second
table would have a foreign key to the first table's primary key, a note field
and a status date field. Rather than four (or however many) pairs of fields
you would have four - or more, however many are needed - RECORDS in this
second table, one for each note. You could use a Form based on the main table
with a subform based on the status table.

 

[ielmrani via AccessMonster.com]

Thank you.
I am sorry I did not mention this before but I already have 2 tables. one
has primary key with note and status date field and the other table has all
the other data (client name, address, phone..etc). I was able to create a
query and link the 2 tables and get the data I need. my challenge is: I want
to use the query in a report to insert (show) only the earliest date and note.
Not all five note and dates.
in this case it'll be

Statusdate2 Note2
7/12/2004 MMMMMMM

 

[John W. Vinson]

Thank you.
I am sorry I did not mention this before but I already have 2 tables. one
has primary key with note and status date field and the other table has all
the other data (client name, address, phone..etc). I was able to create a
query and link the 2 tables and get the data I need. my challenge is: I want
to use the query in a report to insert (show) only the earliest date and note.
Not all five note and dates.
in this case it'll be

Statusdate2 Note2
7/12/2004 MMMMMMM

I'm still not sure I understand. It sounds like you have fields named
Statusdate, Statusdate1, Statusdate2 and so on. But my suggestion (which you
say matches your tables) does NOT have such fields - it has one field
Statusdate and one field Note, and multiple records.

You can get the earliest date either way, though it's simpler with the
normalized table: just use a criterion

=(SELECT Min([StatusDate]) FROM tablename AS X WHERE x.foreignkeyfield =
maintable.primarykeyfield)

on the child table's Statusdate field.

If in fact you have multiple statusdate fields and multiple note fields please
explain.

 

[ielmrani via AccessMonster.com]

The table has Statusdate, Statusdate1, Statusdate2 and so on, each one of
these have dates and notes

Thank you.
I am sorry I did not mention this before but I already have 2 tables. one

[quoted text clipped - 7 lines]

Statusdate2 Note2
7/12/2004 MMMMMMM

I'm still not sure I understand. It sounds like you have fields named
Statusdate, Statusdate1, Statusdate2 and so on. But my suggestion (which you
say matches your tables) does NOT have such fields - it has one field
Statusdate and one field Note, and multiple records.

You can get the earliest date either way, though it's simpler with the
normalized table: just use a criterion

=(SELECT Min([StatusDate]) FROM tablename AS X WHERE x.foreignkeyfield =
maintable.primarykeyfield)

on the child table's Statusdate field.

If in fact you have multiple statusdate fields and multiple note fields please
explain.

 

[John W. Vinson]

The table has Statusdate, Statusdate1, Statusdate2 and so on, each one of
these have dates and notes

In that case your table design IS WRONG, and is the source of your problem.
You will need to write some custom VBA code to parse through the repeated
fields and determine the earliest, or write a UNION query to normalize the
data.

 

[ielmrani via AccessMonster.com]

Thanks for all your help. I am going to try to do a union query cause I have
no clue how to do it in vba code.