earliest date

[Daniel Attard]

Each record in my query has 6 date fields - D1, D2, D3
etc. I would like to define a new field to be
called "D_earliest" that will be the earliest of the 6
date fields. The earliest date can be located in any of
the 6 possible date fields D1 through D6.

I should also add that each record does always contain
the 6 date fields. Some records contain only 3 date
fields (D1, D3, D5), with the other date fields being
null values. I want to define the "D_earliest" field to
pick the earliest of the available date fields.

I hope this makes sense and would greatly appreciate any
assistance offered. Many thanks.

 

[Steve Schapel]

Daniel,

On the basis of what you have told us, I would strongly suspect that
you have a design error with your tables. Is the database structure
set in concrete, or could it be revised? The ideal solution to your
question would be to have these dates as separate records in a single
field in a related table, rather than in 6 separate fields. What you
have now is the kind of thing that might be done in a spreadsheet or
suck like, but is unlikely to work well in a database. If you would
like to explore this avenue further, please post back with more
details about these date fields and what they are all about.

Otherwise, in any case, it would be a bad idea to put the "earliest"
as a field in the table. Once again, this is spreadsheet-think. It
would be a case for creating a user-defined function, which you could
then apply to the table data whenever you needed the earliest date for
your purposes on form or report. I can be more specific about this
workaround,... but let's consider a table design change first.

- Steve Schapel, Microsoft Access MVP

 

[Tim Ferguson]

Each record in my query has 6 date fields - D1, D2, D3
etc. I would like to define a new field to be
called "D_earliest" that will be the earliest of the 6
date fields.

First of all, you need to get the design right. Read up on R theory and
normal form...

In short you need a table that has six records for every one of the main
table. Then the query simply uses MIN() and a GROUP BY.

Hope that helps


Tim F

 

[DannyBoy]

Thank you for taking the time to help me out with this.
Here is my present table structure. There are three
tables, representing a year: tbl2000, tbl2001 and
tbl2002. Each record in the table has 6 date fields,
each representing a different type of deadline: D1, D2,
D3, D4, D5 and D6. These are not required fields.

What I am trying to do is create one report covering all
three years that is sorted by soonest deadline. In other
words, if my soonest deadline is for a year 2001 item, of
the D4 deadline type, I want that item to appear at the
top of my list. If the next soonest deadline is for a
2003 item, of the D2 deadline type, that should come next
in the report. The report will end up being sorted like
this:

tbl2001.D4.Date
tbl2003.D2.Date
tbl2002.D3.Date etc.

If a record in any given year table has 3 deadlines, I
only want the soonest deadline to appear in the report.

I hope you can suggest a better way of structuring my
query. Thank you so much for your help.

 

[Steve Schapel]

Danny,

The good news is that it should be pretty easy to fix your tables.
First point is that all data should be in one table... separate tables
for each year is not a good idea. This is how it should be set up...

RecordID
TheYear
DeadlineDate
DeadlineType

You can use a series of Append Queries to move the data from your
existing tables to this new one. After that, it will be very simple
to get the results you want via a query.

I assume there is actually more data in the database than what you
have let on so far, so if you need more help to fit these suggestions
in with the other data requirements, please post back.

- Steve Schapel, Microsoft Access MVP

 

[DannyBoy]

Thanks again Steve. I think the lightbulb has just come
on inside my head. The reason you are saying that the
data should all be in one table is because there is no
reason for it to be in separate tables? Is that
correct?

When I originally created separate tables for each year
it was because I was combining separate spreadsheets
representing different years of business that did not
initially have consistent data types. As my database
grew, I normalized my data to the point of creating
consistent data types across all the tables, but I
stopped short of combining the various year tables into
one table. I did not do that because in the interim I
had already written various queries and SQL statements
based on the (erroneous?) multi-table configuration.

I think what you are telling me now is that I have to
append all the table information together into one table,
even if it means that I will have to re-write many of the
queries and SQL statements. Does any of this make sense
to you? I am not a programmer myself, and fairly new to
Access, so I'm not even entirely sure I am describing the
situation properly. Thanks for your help.

 

[Steve Schapel]

Danny,

You're partly right. There are actually two related issues here. One
is the 3 tables for the 3 years, which you have mentioned. This is a
"tables as data" error. In other words, which year the records belong
to is data, and as such should be properly represented by data entry
*into* the table(s), not *by* the tables themselves. The second issue
is the 6 date fields. This is a "fields as data" error. In other
words, which type of deadline is data, and as such should be properly
represented by data entry *into* one field in the applicable table,
not *by* the fields themselves.

I just *knew* you must have started out with a spreadsheet

Any extra time and effort now to restructure your tables will pay you
back multiple times later on.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Thank you Steve. I understand your point fully about
the "tables as data" error. I will restructure and
combine the various year tables into one table. Your
other point about the "fields as data" error I am not
quite clear on.

If I follow your suggestion and represent the type of
deadline by an entry *into* one field in the applicable
table, this will mean that in instance where there are
currently 6 deadlines for the same RecordID, I would now
have 6 entries with the same RecordID and each one
represents the different deadline type.

Are you suggesting that in addition to appending each of
the year tables into one table, I should also split that
one table into two? The two resulting tables would
consist of:

tbl1.RecordID
tbl1.TheYear
tbl1.ValueBefore
tbl1.ValueAfter

and

tbl2.RecordID
tbl2.TheYear
tbl2.DeadlineDate
tbl2.DeadlineType

Since there can only ever be one ValueBefore and one
ValueAfter per RecordID per Year, should my new tables be
structed as shown above (to avoid repetition of the
ValueBefore and ValueAfter amounts for each deadline
date? Or is this carrying normalization rules too far?
Should I simply have one table as follows:

tbl1.RecordID
tbl1.TheYear
tbl1.ValueBefore
tbl1.ValueAfter
tbl1.DeadlineDate
tbl1.DeadlineType

I think I know that answer to my own question and you are
going to say that the date should be in separate tables
because if it were all in the same table as shown above,
this could repeat the ValueBefore and ValueAfter amounts
up to six times per RecordID if all of the 6 optional
date types have entries. Thanks again. Your help is
much appreciated!

 

[Steve Schapel]

Thank you Steve. I understand your point fully about
the "tables as data" error. I will restructure and
combine the various year tables into one table. Your
other point about the "fields as data" error I am not
quite clear on.

If I follow your suggestion and represent the type of
deadline by an entry *into* one field in the applicable
table, this will mean that in instance where there are
currently 6 deadlines for the same RecordID, I would now
have 6 entries with the same RecordID and each one
represents the different deadline type.

Are you suggesting that in addition to appending each of
the year tables into one table, I should also split that
one table into two? The two resulting tables would
consist of:

tbl1.RecordID
tbl1.TheYear
tbl1.ValueBefore
tbl1.ValueAfter

and

tbl2.RecordID
tbl2.TheYear
tbl2.DeadlineDate
tbl2.DeadlineType

Since there can only ever be one ValueBefore and one
ValueAfter per RecordID per Year, should my new tables be
structed as shown above (to avoid repetition of the
ValueBefore and ValueAfter amounts for each deadline
date? Or is this carrying normalization rules too far?
Should I simply have one table as follows:

tbl1.RecordID
tbl1.TheYear
tbl1.ValueBefore
tbl1.ValueAfter
tbl1.DeadlineDate
tbl1.DeadlineType

I think I know that answer to my own question and you are
going to say that the date should be in separate tables
because if it were all in the same table as shown above,
this could repeat the ValueBefore and ValueAfter amounts
up to six times per RecordID if all of the 6 optional
date types have entries. Thanks again. Your help is
much appreciated!

 

[Steve Schapel]

Danny,

It would help in answering your question if you could let us know what
ValueBefore and ValueAfter is all about?

Also, in re-considering my earlier comments, I am now not sure that a
TheYear field is necessary, now that you are re-uniting your data, as
the DeadlineDate fields will automatically indicate the Year anyway,
won't they?

- Steve Schapel, Microsoft Access MVP

 

[Daniel Attard]

Thanks again Steve. I'll give you some more background
to help you understand my problem. Currently, each table
in my database represents a different block of work, each
record in the table represents a property tax appeal for
a given property. For each property represented by a
record in the year table, there are 6 different date
fields. Each date field represents a different type of
deadline for the appeal. The ValueBefore and ValueAfter
simply refer to the value of the property tax assessment
before the appeal, and the value of the assessment after
the appeal (i.e. Reduced from $1,000,000 to $900,000).

In my 2003 table for example, there are 1,300 records.
This means that I have 1,300 properties under appeal for
2003. For each property, I need to track the 6 different
possible date fields. The 2002 table has 1,000 records
and consists of the same type of information, except that
the records relate to appeals for the 2002 taxation
year. Some properties are appealed from year to year
which means that they have an entry in each table. Some
only appear in one table because they were only appealed
for one year.

Is this enough information for you to understand my
current table structure and the reasons for it?
Ultimately what I am trying to do is to retain my current
table structure, and then create a report sorted by
deadline date, regardless of which year the deadline
relates to. Thanks for your patience.