473,233 Members | 3,752 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

ORDER DATABASE FIELDS by DATE VALUES for EACH RECORD

Hi all....I have a feeling this is going to be one of those twisted query
questions, but here it goes anyways....

I want to generate a report that shows the chronology of events (represented
by field names). Essentially, I would like to sort the DATE FIELDS for each
record in the table by the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4
I'm doing this in ACCESS, so even if it's possible in SQL SERVER only, I'd
like to see the logic used.

Thanks in advance!

joe
Nov 12 '05 #1
4 6264
"Joe User" <jo*@user.com> wrote in
news:bp**********@tribune.mayo.edu:
Hi all....I have a feeling this is going to be one of those
twisted query questions, but here it goes anyways....
Twisted question yes, absolutely not a query question.
No query can make up for a bad table structure, because your dates
should be in a linked child table.

Since you are dealing with a report, you could write code to do the
following steps for each record, in the Detail Format Event.

Read the 5 dates and their field names into an 5x2 array.
Sort the array
concatenate the five field names and some spacing into a variable.
set the value of a textbox to the variable value.
end the sub, which will then print the record.

If you insist on doing something in SQL, which would be worse than
twisted, create 5 queries, each dealing with one field name

SELECT RecordID, "Date 1" as Fieldname, date1 from twistedtable
SELECT RecordID, "date 2" as Fieldname, date2 from twistedtable

then create a union query to combine the 5 queries, ordered by
recordID, datevalue
Now create a report that take thes 5 fieldnames and prints them
horizontally.

Add this as a subreport to your main report.

I want to generate a report that shows the chronology of
events (represented by field names). Essentially, I would
like to sort the DATE FIELDS for each record in the table by
the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4
I'm doing this in ACCESS, so even if it's possible in SQL
SERVER only, I'd like to see the logic used.

Thanks in advance!

joe


Nov 12 '05 #2
"Joe User" <jo*@user.com> wrote in message news:<bp**********@tribune.mayo.edu>...
Hi all....I have a feeling this is going to be one of those twisted query
questions, but here it goes anyways....

I want to generate a report that shows the chronology of events (represented
by field names). Essentially, I would like to sort the DATE FIELDS for each
record in the table by the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4


SQL Server is not required. You could do something like...

SELECT Table.Field1, "Table.Field1Name" AS MySource
FROM Department
UNION ALL
SELECT Table.Field2, "Table.Field2Name" AS MySource
FROM Department
ORDER BY MySource;

Of course, if you normalize, it's a walk in the park...
Nov 12 '05 #3
Looks like a pretty standard bubble sort problem to me. Im assuming
that you have a table with the following schema:

tblEvents
-----------------
Rec_ID AutoNumber
Date1 Date/Time
Date2 Date/Time
Date3 Date/Time
Date4 Date/Time
Date5 Date/Time

I can't imagine trying this in sql, possibly with some messy bunch of
nested queries testing each date against the other and allowing them
to percolate into the proper order but i wouldnt want to read it, much
less write it. However, If you're willing to use VBA then it becomes
pretty simple.

There are 2 ways that I can think of, at the point of entry, as a new
date is recorded would be the easiest, because you would only have to
test the date against the previous date once to determine if it is
larger or smaller.

In this case you would always have the earliest date in Date1 and the
latest date in Date5. Since I dont know your intention I can't tell if
that would be acceptable, but if it is then this would be done prior
to storing the new date so that the order can be maintained in the
table rather then calculated on the fly...

dtMyNewDate = Date

For x = 1 to 5
strFieldName = "Date" & x
'If its an empty field just store the date
If Me(strFieldName).Value = "" Then Me(strFieldName).Value =
dtMyNewDate
'Otherwise test to see if its larger then me and if so, switch em
Else If dtMyNewDate < Me(strFieldName).Value Then
dtTmpValue = Me(strFieldName).Value
Me(strFieldName).Value = dtMyNewDate
dtMyNewDate = dtTmpValue
End If
Next

Debug.Print Me!Rec_ID.Value, Me!Date1.Value, Me!Date2.Value,
Me!Date3.Value, Me!Date4.Value, Me!Date5.Value

If you use this logic then the first time you add a date Date1 would
be null and the date would be stored there. The second time Date1
would not be null and dtMyNewDate would be tested against it, If its
not less then the loop would repeat and since Date2 is null it would
be stored there. If dtMyNewDate IS less then the current date stored
in Date1 is copied to a temporary variable and replaced by
dtMyNewDate. Then the dtTmpValue is placed in dtMyNewDate for further
comparison through the loop, in this way the largest value will always
end up in Date5 and you wont have to figure out what order to display
them in.

If you have to use the dates the way they are for other reasons, then
you can modify this logic so that instead of switching the values
around you are switching the fieldnames around until they are ordered
as necessary and then display them on your report.

DISCLAIMER: This is untested code and should be considered psuedocode,
solely to demonstrate the sorting logic. I code in a coupla languages
and am always having to correct my syntax for VBA, especially with
access.

If you want to post back with exactly how you manage your data entry
and if you can't change the fields around I'll be glad to work it out
for you some more, and heck, I'll even test it first :)

HTH,

John

jobrien AT acscience DOT com

"Joe User" <jo*@user.com> wrote in message news:<bp**********@tribune.mayo.edu>...
Hi all....I have a feeling this is going to be one of those twisted query
questions, but here it goes anyways....

I want to generate a report that shows the chronology of events (represented
by field names). Essentially, I would like to sort the DATE FIELDS for each
record in the table by the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4
I'm doing this in ACCESS, so even if it's possible in SQL SERVER only, I'd
like to see the logic used.

Thanks in advance!

joe

Nov 12 '05 #4
OK - well, I see I have a normalization issue going on. In part of my
defense I will say that I've inherited this DB from a bunch of accountant
types, and I set out on this project as a newbie and didn't realize that
date tracking would even become an issue. However, I do plan on eventually
moving this over to MS-SQL server and the idea of putting date fields into a
child table never even occured to me...In fact, while I'm writing this I
can't right away see how that would help me, but I blindly know that
normalizing things makes DB life better, it's just not apparent to me yet.
When you all set tables up in a DB do you automatically make all your date
fields reside in a child table?

Thanks for the help guys, I am going to see what I can do to with this and
think more about normalizing. I will probably have a few more questions
before it's all said and done.

Thanks again!

Joe
"Bob Quintal" <bq******@generation.net> wrote in message
news:ce******************************@news.teranew s.com...
"Joe User" <jo*@user.com> wrote in
news:bp**********@tribune.mayo.edu:
Hi all....I have a feeling this is going to be one of those
twisted query questions, but here it goes anyways....


Twisted question yes, absolutely not a query question.
No query can make up for a bad table structure, because your dates
should be in a linked child table.

Since you are dealing with a report, you could write code to do the
following steps for each record, in the Detail Format Event.

Read the 5 dates and their field names into an 5x2 array.
Sort the array
concatenate the five field names and some spacing into a variable.
set the value of a textbox to the variable value.
end the sub, which will then print the record.

If you insist on doing something in SQL, which would be worse than
twisted, create 5 queries, each dealing with one field name

SELECT RecordID, "Date 1" as Fieldname, date1 from twistedtable
SELECT RecordID, "date 2" as Fieldname, date2 from twistedtable

then create a union query to combine the 5 queries, ordered by
recordID, datevalue
Now create a report that take thes 5 fieldnames and prints them
horizontally.

Add this as a subreport to your main report.

I want to generate a report that shows the chronology of
events (represented by field names). Essentially, I would
like to sort the DATE FIELDS for each record in the table by
the order of the DATES in those DATE FIELDS.

For example:

record ID= 354
date1= 10/2/2003
date2= 9/25/2003
date3= 8/13/2003
date4=11/15/2003
date5=05/09/2003
I would like the output to look like:

recordID:354 date5 date3 date2 date1 date4
I'm doing this in ACCESS, so even if it's possible in SQL
SERVER only, I'd like to see the logic used.

Thanks in advance!

joe

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: svilen | last post by:
hello again. i'm now into using python instead of another language(s) for describing structures of data, including names, structure, type-checks, conversions, value-validations, metadata etc....
1
by: David Thompson | last post by:
Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like.... Start by asking which...
2
by: MVA | last post by:
Hi all I have a database where in a table (tbl_Members), there are 2 date fields (DateOfBirth and DateJoined), and also 2 fields which upon entering the data in the form, it automatically works...
7
by: Not Me | last post by:
Hi, Having a table with some duplicate ID's (different data tho), how can I return the list but with only one record from each ID? Would this be using the first() function and grouping? ...
2
by: brucedodds | last post by:
This post is for senior developers. I'm an Access contractor. This morning I see a listing for a position that seems to require development of a self-modifying database. The application may need...
3
by: moriman | last post by:
Hi, I am fairly new to the intricacies of relational databases so please be gentle with me ;-) I have just taken on a delivery run. The same (mostly) customers receive deliveries Monday to...
8
by: kaosyeti | last post by:
i have a (hopefully) small problem. i have created a system where a user enters customer information into a table through a form. this table has no primary key. there are 9 fields on the form to...
29
by: MP | last post by:
Greets, context: vb6/ado/.mdb/jet 4.0 (no access)/sql beginning learner, first database, planning stages (I think the underlying question here is whether to normalize or not to normalize this...
16
by: Ian Davies | last post by:
Hello Needing help with a suitable solution. I have extracted records into a table under three columns 'category', 'comment' and share (the category column also holds the index no of the record...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.