Date Order

[Chey]

Is there a way to take a row of information in a qurey and put it in date
order.
For example I use Travel Authorization numbers for each trip a traveler goes
on. I log in there trip information based on Date the Credit Card was run
and what the purchase was and the amount. So each piece of information is in
a new field but same record (you could say, I made a form) Now After I enter
this information is there a way on my report to make each purchase go in date
order? As of now I have the fields on my report where I want. Then I print
it and it comes out properly. However they are not in date order, I know
that where I put the field is where the information is going to be viewed. I
was just wondering if there was a code to put one field before another
dependet on the date?

Thanks for taking time to help me.
Chey

 

[Steve Schapel]

Chey,

In the design view of the report, select 'Sorting and Grouping' from the
View menu, and then enter the name of your date field in the dialog box.
The data in the report should then be printed in date order. I assume
the date field is set up in the table as a Date/Time data type?

 

[Chey]

Well I made this a little more difficult than it probably should be. I have
the fields labeled as followed
Date Ticket Purchased
Ticket Amount
Ticket Vendor
Car Rental Date
Car Amount
Car Vendor
And so on with many different vendors.

Is there a way of placing those in order. There are about 15 different date
fields. I have them all placed on a report in a certain order. Is there a
way of saying if Ticket date is less than Car date then to place the ticket
date first then the car date?
That is kind of where I am going.

Thanks for your time.
Chey

 

[Steve Schapel]

Chey,

Well, it would be possible. But it would involve basing your report on
a very complex Union Query. Either that, or write some code to use 15
append queries to throw all of your data into a temporary holding table
to normalise the data and base your report on that.

Any chance at this stage of revising the table structure. It would be a
lot better if these events were separate records rather than separate
fields, something like this...
TransactionDate
TransactionType
Amount
Vendor
.... so, whether it's ticket or car or whatever is recorded in the
TransactionType field. Not just for the example of sorting the
transactions by date, as per your example, but this would make your data
much more usable in other ways as well.

 

[Chey]

I started to play around with that I found a problem. I am still pondering
how to figure it out.
On my report I have my main report that I have separted
Date Explanation Miles Cash Lodging MIE Other

Well Date and Explanation kind of explane itself
When I put together my table I placed
Date
Vendor
Amount

Well If the vendor is XYorZ it may fall under Lodging or other.
I thought of creating a table of lodging and writing a code that if any of
those names are in there then place under the lodging. This may sound
hoaking now, but I am still thinking.

The Date Vendor and Amount will be placed on a subform.

Let me know if you have any good Ideas for me.
Thanks

 

[Chey]

okay so I am figuring this out. Now I have a table
Amount
Vendor
Date
TA Number
Cash
Credit
Hotel

Then I am making some more tables
Hotels
Cars
Fuel Stations

On my report I put the Date vendor and then amount where a dollar amount
could appear. How can I based on what the vendor is detrimen which amout
should show.

The reason for it is an hotel amout would not appear under Other. My report
idomizes the amount. Also a traveler may pay cash for there hotel, because
they forgot there state credit card i am going to make a check box to say
Cash or Credit card. I want it always to default to credit card.
So right now under each catagory I have are Miles Rate Cash Fare
Lodging MIE Other. Under each of these I put the amount so now if I
use my table lodging and if Vendor is in that table then only show the amount
under lodging. I hope this makes some sense. Then I am going to have a car
table same thing if the car vendor is in that list then under other. Unless
I have the cash button checked then it needs to go under cash. this will
apply to every charge. 60% of my travlers use there state credit card
however 40% of the time they forget. I just need to cover all my bases.
Thanks

 

[Steve Schapel]

Chey,

Well, I'm sorry I still haven't fully grasped the full understanding of
the data you are working with. But all I can do is repeat what we said
before... If you are recording information related to payments, then
you seem to be classifying the payments according to which fields (and
even which table). This is not a good idea. Forget the forms and
reports for now - get the tables right first. It is much better to work
it out so that each payment is a separate *record*, and you have a field
in each record that you use to classify the payment (e.g. what the
payment was for, and how paid).

 

[Chey]

I got the one part figured out and it works great. I made a table with the
fields date vendor and so on. Well now Somehow I have to add flight
information in there. So now that I have all the vendors working how can I
take another table with fields
Date Leave Date Return Time Leave Time Return Orgin and Destination to
follow in with the vendors. I tried some different things but the dates
would not line up they went side by side. Is there a way to do this?

 

[Steve Schapel]

Chey,

So far, I don't really even know what this application is all about.
But it is starting to look like some sort of travel reservation database
- is that right? When you mention about flight indformation, it seems
to me that this is probably related to a client booking - is that right?
Can you give me a "plain language" description of what the overall
purpose of the database is? And can you give me an outline of the main
tables you have in there so far? As regards the flight information, I
guess the first question would be whether it could happen that you need
to record more than one flight leg for any given client/booking?

 

[Chey]

You are right.
I have a travel database that was working fine however our finance dept
wants what we call Travel Authorizations in Date order. This is an idomized
list of all expeditures plus the itnerary.
So on that note.
I have 3 main tables.
TA_Numbers
Date_Vendor_Amount
Travel_Request
The traveler first assigns themselves a TA Number when they do that they all
type in where they are traveling from where they are going and all the
dates. This then goes into another table where they type in there travel
requests. What time they want to leave and return, if they want a car or/and
hotel. Such stuff like that. This then prints out a travel request for me
to book. Well after all of this I need to make a pre approval ta authorizing
the travel. This is where I type out what the estimated cost is going to be.
Along with the vendors names. I don't have to put the vendors names but it
helped me sort things out later. Then when I get the final itinerary I type
it into what use to be individual fields. Now i don't know where I am going
to put that. That is why I made the flight_information table. That is in a
test run right now. I would like to put this under Date_Vendor_Amount. Then
go back after the flight is complete and fill in the actuall cost and the
date. So now that works great on my Final Travel Authorization that I
submitte to finace to be paid. However I also need the interary on there in
date order. So if mary jane flew out on 9/6 @ 4:45pm
Then landed on 9/6 @ 5:30pm
Hotel Charge 9/7
Car Charge 9/8
Flew to a new city on 9/8
Landed on 9/8
Charged other hotel 9/9
Some where along that line
So again I have the amount taken care of but not the itinerary.
I then print out the Travel Authorization for the Traveler to sign and then
I submitte it to Finance. In turn this infomation helps me with my yearly
budget and how much each traveler has spent. Last year I was able to
determine how much each traveler spent on each vendor. Anyways right now I
just need to get vendor and TA number table to come together.
Thanks for all your time.
Chey

 

[Steve Schapel]

Chey,

As regards:
Flew out on 9/6 @ 4:45pm
Then landed on 9/6 @ 5:30pm
Hotel Charge 9/7
Car Charge 9/8
Flew to a new city on 9/8
Landed on 9/8
Charged other hotel 9/9

.... where is this data? Wherever it is, it must be that each of the
lines in your description above is represented by a separate record in
your table. Ok? So the fileds in that table would include something
like this (I guess)...
ChargeID
TA_Number
ServiceDate
Vendor
EstimatedCost
ActualCost
Notes

Is that vaguely similar to what you've got?

 

[Chey]

Okay I see where this is going now. No I don't have my table exactly like
that but close. I can change it like that and the information would still be
valid. How can I still keep the itinerary portion in that table. The
example I gave you right now is not in any table. I need to put it in one.
So I am still doing good on the vendor portion. How do I name the fields to
include the itinerary part. I still need this in date order.
Thanks so so much for your time.

 

[Steve Schapel]

Chey,

If each item in the travel authorization is a separate record, then it
is very easy to sort it in date order.

I'm not really sure what you mean about the Itinerary. Isn't the
itinerary simply the list of the items for a particular travel
authorisation. Can you give examples please?

 

[Chey]

Well since you already know the other part I am working with Now the itinerary
When I receive an Itinerary it is as followed
Date Leave
City Leave
Time Leave
Date Arrive
City Arrive
Time Arrive

it is in this formate at least twice going and returning
Sometimes I have a traveler bouncing all over the place. The most I have
had to deal with is 5 variations of taking off and landing. On a normal trip
the traveler has already put in the date leave and return along with the city
departing from and returning. I call this table TA. This is where they
assign there Trip specs. The layout of that table is
TA Number
Travelers Name
Date Leave
Date Return
Orgin
Destination

But if they have more flights I need to type that in also.

 

[Steve Schapel]

Chey,

From what I understand, I would set up a Legs table, where each segment
of a travelers trip forms one record. Pretty much as you described the
fields in the Itinerary table, except I don't know why you have the date
and time fields separated, since the date and time can both go in the
one field. And you would need to add a TA Number field in there, so
that the records can be all associated together as part of the one trip.
So, in the case of the traveler with 5 segments to the trip, that
would mean 5 records in the Itinerary table. The TA table should not
have the date fields or the origin and destination fields, as this is
replicating data that should be in the Itinerary table.

 

[Chey]

okay I am not starting to understand can you walk me through on how to do
this. How do I set up the legs table. I have created the to tables. The
vendor table and the itinerary table. Now what?

 

[Steve Schapel]

Chey,

What fields do you have in the Itinerary table? I'm sorry, I think I
may have confused the issue by introducing the concept of Legs. I was
really just trying to distinguish between the idea of regarding an
Itinerary as being a single entity, which would tend to make you want
one record in a table to cover an entire trip, versus the idea of
regarding an itinerary as composed of a series of legs, each one being a
separate individual record in the table. So, what have you got in
Itinerary so far.

 

[Chey]

The fields that I have in the itinerary table are
Date Leave
Date Arrive
Time Leave
Time Arrive
Depart From
Arrive To
Date Leave Time Leave and Depart from need to be together
Date Arrive Time Arrive and Arrive to need to be together
Are you able to still help me. Sorry it took so long to get back. I was
away from the office for 5 days. Thanks
Cheyen

 

[Steve Schapel]

Chey,

I am very sorry, I can't see what really is the problem here. I think
maybe you are trying to make it more complicated than it needs to be.
As I said before, this Itinerary table seems to serve the purpose well.
All you need as another field, which I think is TA, so that all
Itinerary records are tagged to a particular TA, and therefore the
specific itinerary details for any given trip can be easily extracted
from the Itinerary table based on the TA value. So, what am I missing,
or what is the specific difficulty you are experiencing?