Sorting in date order

[Guest]

I have a txt import file that has 2 date fields (linked in as a Text field
using the import specs, if imported as a Date, then got an error:#Num#, i
believe it was).

I have a planned date and actual date
The user would like to have each planned date fall into its own column...so
the column April should have only records that were planned for completion in
April 05 for example., and each subsequent month (May, Jun, etc) falling
into the column for that month. I am looking at tracking some 14 month
columns in all and having each record place its Planned Date in the correct
monthly column.
I have to be able to handle crossing into 2006.
Thanks for your help

 

[John Vinson]

I have a txt import file that has 2 date fields (linked in as a Text field
using the import specs, if imported as a Date, then got an error:#Num#, i
believe it was).

I have a planned date and actual date
The user would like to have each planned date fall into its own column...so
the column April should have only records that were planned for completion in
April 05 for example., and each subsequent month (May, Jun, etc) falling
into the column for that month. I am looking at tracking some 14 month
columns in all and having each record place its Planned Date in the correct
monthly column.
I have to be able to handle crossing into 2006.
Thanks for your help

If the date is stored in a Text field, you will need to convert the
date to a real date. Could you post the actual appearance of the
imported text field? Does it contain a full date, or just a month and
year, or just a month?

John W. Vinson[MVP]

 

[Guest]

John,
Thanks for your reply...
It is in this format: 05-Apr-05

If I need to convert to Date, what is the easiest way to do that since I
will continue getting the data from a linked file, and would want to
dynamically do any conversion going forward???

 

[John Vinson]

John,
Thanks for your reply...
It is in this format: 05-Apr-05

If I need to convert to Date, what is the easiest way to do that since I
will continue getting the data from a linked file, and would want to
dynamically do any conversion going forward???

You can sort dynamically using the text field by creating a calculated
field in your query:

SortDate: CDate([textdate])

I'm not sure why you're getting errors on the linked file!

John W. Vinson[MVP]

 

[Guest]

John,

Thanks for your response and support

Below is a sample of what i am trying to accomplish:
I have one input file with lots of dates, i would like to have each PO fall
into a column designated for that month....
Purchase Order # Mar Apri May Jun
123 3/1/2005
234 3/3/2005
345 4/4/2005
543 5/4/2005
265 1/6/2005

John,
Thanks for your reply...
It is in this format: 05-Apr-05

If I need to convert to Date, what is the easiest way to do that since I
will continue getting the data from a linked file, and would want to
dynamically do any conversion going forward???

You can sort dynamically using the text field by creating a calculated
field in your query:

SortDate: CDate([textdate])

I'm not sure why you're getting errors on the linked file!

John W. Vinson[MVP]

 

[John Vinson]

John,

Thanks for your response and support

Below is a sample of what i am trying to accomplish:
I have one input file with lots of dates, i would like to have each PO fall
into a column designated for that month....
Purchase Order # Mar Apri May Jun
123 3/1/2005
234 3/3/2005
345 4/4/2005
543 5/4/2005
265 1/6/2005

Put a calculated field in your Query by typing

MonthName: Format([PurchaseDate], "mmm")

This will contain the text strings "Jan", "Feb", ..., "Dec".

Create a Crosstab query using this field as the column header ahd the
PO # as the row header.

You'll want to use the crosstab query's Headers property to sort the
columns - set it to

"Jan";"Feb";"Mar";"Apr"; <etc>

otherwise the months will appear in alphabetical rather than
chronological order.


John W. Vinson[MVP]

 

[Guest]

John,

Thanks again, one more refinement...
What I get is the number 1 (true) in each month were that mmm exists. What i
would like to do is place the actual date into the monthly column. For ex.
let's say that my 1st entry is 03-Jan-05, then in the Jan month column, I
would like to see 03-Jan-05 opposed to what currently displays now "1".
Again, thanks for your help...

John,

Thanks for your response and support

Below is a sample of what i am trying to accomplish:
I have one input file with lots of dates, i would like to have each PO fall
into a column designated for that month....
Purchase Order # Mar Apri May Jun
123 3/1/2005
234 3/3/2005
345 4/4/2005
543 5/4/2005
265 1/6/2005

Put a calculated field in your Query by typing

MonthName: Format([PurchaseDate], "mmm")

This will contain the text strings "Jan", "Feb", ..., "Dec".

Create a Crosstab query using this field as the column header ahd the
PO # as the row header.

You'll want to use the crosstab query's Headers property to sort the
columns - set it to

"Jan";"Feb";"Mar";"Apr"; <etc>

otherwise the months will appear in alphabetical rather than
chronological order.


John W. Vinson[MVP]

 

[John Vinson]

John,

Thanks again, one more refinement...
What I get is the number 1 (true) in each month were that mmm exists. What i
would like to do is place the actual date into the monthly column. For ex.
let's say that my 1st entry is 03-Jan-05, then in the Jan month column, I
would like to see 03-Jan-05 opposed to what currently displays now "1".
Again, thanks for your help...

Please open your query in SQL view and post the SQL text here.

John W. Vinson[MVP]

 

[Guest]

John,

I got it to work;

TRANSFORM First(Query1.Planned) AS FirstOfPlanned
SELECT Query1.Desc, Query1.Fcast, Query1.A, First(Query1.Planned) AS [Total
Of Planned]
FROM Query1
GROUP BY Query1.Desc, Query1.Fcast, Query1.A
PIVOT Query1.Month In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for your help