Sorting two fields

[darlaj5]

If I have two fields 1-pick up times and 2- drop off times is there a way I can sort the page by time combining the two fields?

Thank you for your time

[June7]

You could but would the result make sense? Why are you wanting to do this, what is it you need to accomplish?

[darlaj5]

Because sometmes a drop off occurs during a pick up schedule so i have to sort the table by times but the pick up times and drop off times are two seperate fields. I need to sort the page by time or combine the two fields when setting the sort command.

Is there a way can you help? Thank you for your time.

[alansidman]

You will need to create two queries. First one will have only pickup time field. Create an alias for the field, ie. SchedTime: PickUp

The second one will have only drop off time field. Create an alias for this field also. It needs to be the same as the first, ie. SchedTime: DropOff

Save both queries. Now create a Union query of the two queries you created and the information will be in one query. You can add an Order By clause at the end of the second part of the Union, ie. Order By SchedTime (ascending)

Alan

[nicknameoscar]

Hi,

I haven't fully thought this out but what about having a separate table where there is only one time for each transaction. The part I haven't thought out is whether to have another field in the new 'Time' table that codes the time as a pick-up or drop off or whether the original table should have the pick-up/drop off code fields.

Does the time include the date? If not then you might want to date stamp each new 'Time' record (additional field) so you can limit the query returns to a certain day or days if desired.

[darlaj5]

Thank you both for your help this will really help. The only thing is how do you create a alias? I'm new to this and self taught and I really appreciate your help. Is there an advanced tutorial for access?

Also do I have to change the fields in the table to reflect the above mentioned field names?

Again thank you.

[June7]

An alias field is a field constructed with an expression, usually to manipulate data with a calculation to create new value. Example would be - Age: DateDiff("yyyy",[BirthDate],Date()). You would type this entire expression in the Field row of Query Designer. If you forget Age: then Access will use something like Expr1: for you. Alansidman's suggestion is a way to get these time values into a common field named SchedTime. In this case the expression is simply a reference to existing fieldname, no calculations taking place.
Access Help has more info, search 'select query'.

However, I think might be able to do the UNION on the tables without the intermediate queries. There is no wizard or designer for UNION queries, must type into the SQL View window of Query Designer. Something like:

SELECT ID, "Pickup" As Type, PickupTime As SchedTime FROM tblPickup
UNION SELECT ID, "DropOff", DropoffTime FROM tblDrop;

This is really how the table structure should be to begin with.

Something to be aware of about UNION, it will not show multiple identical records.

Tutorials are all over the place, start at http://office.microsoft.com/en-us/tr...010158283.aspx
and http://www.techonthenet.com/sql/union.php

[darlaj5]

Thanks Adam I appreciate your input. This data base I'm trying to put together is actually only a small piece of the pie to eliminate redundant tasks. We use School Master for our transportation needs. I'm creating this data base to minimize the users on School Master. Its complicated and I'm sure difficult to relate to, never the less it will satisfy our needs. I think I'm just challenged and determined to understand this program. I have actually set up the data base and it works and serves the purpose now. THe only thing I haven't put together yet is the sorting but based on the info above I'm sure it will come together. If not I'll just run one report for pu and one for do. Thank you to all for you input I love this site. I'm going to take the tutorials and learn this program. Thanks for the link, I'm obsessed with this access now, lol. Thsi post is for both of my threads....