Hi DMX...

I've nearly completed the basic structure of my new virtual airline... but have come very unstuck on one or two bits... Thanks to DMX Zone I've sorted most of them and now am left with one problem...

The site has been created in DMX2004 with Access DB's...

I'm trying to create a page that displays all the pilots (from the members DB) and their total flight time (from the Flights DB) in a simple table (repeat region). Example

rgeorgiou 121
ftreadwell 60
mrsmith 32

The userID field is simple, it's in a db called members... I've just set up a repeat region that lists them all 10 at a time.... Simple thur far..

The total flight time is totally confusing me... I've got a db called flights which includes all flight that all pilots have completed. The field I'm interested in is called FlightDuration and it contains the length of the flight in time/date format... This db also contains a userID field.

I need to total up the flight times for each pilot and then put the total (in minutes) next to the pilot's userID.

Thus far I've created two record sets. One for the members db (no problem) and one for the flights db...

Thus far the recordset contains the following sql...

SELECT (sum(datepart('h',FlightDuration)*60 + (datepart('n', FlightDuration)))) as Total_Minutes
FROM Table1

(Thanks to Chez from DMX Zone!!)

which works a treat...

However I need to put something in the sql that basically says "WHERE userID from MembersDB = UserID from FlightsDB"

I just can't get it right...! and I'm now going grey!

If anyone has any suggestions please let me know...

Thanks again

A truely wonderful resource!

Richard Georgiou

Hi Richard,

Take the code that I gave you for the sql command that you have filtered by the user is

Select blah blah
from table1
where userid = blah blah

and replace the where line with:

order by user_id

then, in the select part, add the following:

,Userid

if this shows the id against the total flying time against user id let me know and then I will explain how to join the tables to get the username.

Just want to be sure that the order by will work first.

By the way, my name is Vince, the chez bit is french for My home (hence my site Chez-vince) lol.



Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Hi Vince,

Thanks again for your help..

I've tried adding ,userid to the end of the SELECT statement but I receive the following error:

[Microsoft] [ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'userid' as part of an aggregate function.

I tried adding userid, straight after the SELECT but that produced the same error.

Thanks again
Richard

Ijust noticed that I gave you some bad info.... you must add group by userid (userid being your user Id field) in place of the where statement instead of order by

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Edited by - bakerv on 03 May 2004 10:45:02

Hi Vince,

Thanks for the latest info... The flight duration is now displaying correctly for all the pilots but I can't seem to link in the userid field correctly...

Thus far I've got the sql you gave me for the TotalDuration (in rsTotal) which is working a treat but the userid is from rsMembers and is staying the same...

So the table looks something like this:

userID Total Flight Duration (Minutes)
==============================
rgeorgiou 199
rgeorgiou 60
rgeorgiou 71
rgeorgiou 62

I've checked the flightdurations and they are bang on, all I need to know now is how to make the userID change to correspond to the FlightDuration field....

I really do appreciate the help Vince and will be adding your name to my credits!

Thanks again
Richard

Edited by - rgeorgiou on 03 May 2004 11:30:16

can you show your two table structures, i.e. flights and members like the example below:



Field Name | Field Type

Userid | numeric
Usrname | text

etc.

Also, can you post the sql you have in your current recordset. From this we should be able to cure your problem.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Hi Vince,

Thanks for the fast reply!

Here is the info you asked for...

DB Members
=======
ID - Autonumber
firstname - Text
lastname - Text
userid - Text
psswrd - Text
gender - Text
age - Text
county - Text
country - Text
occupation - Text
email - Text
simexperience - Memo
hearaboutus - Memo
datejoined - - Text
paid - Text

DB Flights
=======
ID - Autonumber
userid - Text
flightno - Text
aircraft - Text
departureairport - Text
departurerunway - Text
departuretime - Text
arrivalairport - Text
arrivalrunway - Text
arrivaltime - Text
approachtype - Text
flightduration - Date/Time
fuelused - Text
passengers - Text
metar - Text
notes - Memo

I have added the two db to my page by creating the following recordsets:

rsMembers
=======
SELECT *
FROM Table1

rsTotal
=====
SELECT (sum(datepart('h',FlightDuration)*60 + (datepart('n', FlightDuration)))) as Total_Minutes
FROM Table1
GROUP BY userid

I know my choice of datatypes are strange but I validate by DWMX's validate behaviour attached to the form which works very well for me.

Thanks again for your help!!

Richard

In theory, the following should work fine, it should give you a recordset with a total flying time in minutes for each pilot.

SELECT (sum(datepart('h',FlightDuration)*60 + (datepart('n', FlightDuration)))) as Total_Minutes, userid
FROM Table1
GROUP BY userid

If it doesnt, let me know.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Vince!!

You're a star!

All works a treat...!

I thought I'd tried this previously but couldn't seem to add the ,userid to the end of the SELECT statement... Still works now and I thank you!

Thanks again Vince, I appreciate all your time and help...

I'll let you know once it's up and active, be about a month - 2 months..

Thanks again
Richard Georgiou

Edited by - rgeorgiou on 03 May 2004 12:46:49

No worries,

Glad to help and see it working. These sort of sql queries can keep you stumped for a long time....I remember only too well..

Look forward to seeing the finished site.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Hi Vince!!

(hehe!)

I don't suppose there is an easy way to add an ORDER BY Total_Minutes to the SQL below:

SELECT (sum(datepart('h',FlightDuration)*60 + (datepart('n', FlightDuration)))) as Total_Minutes, userid
FROM Table1
GROUP BY userid

I tried adding it but got an error

Syntax Error, Missing Operator in query expression 'Total_Minutes GROUP BY userid'

If this is complex then don't worry as it's not overly important...

Cheers!
Rich

Because Total_Number is a sydonym for the result of the formula it cannot be used in any other part of the sql query. However, you can use the formula in the order by command:

SELECT (sum(datepart('h',FlightDuration)*60 + (datepart('n', FlightDuration)))) as Total_Minutes, userid
FROM Table1
GROUP BY userid
ORDER BY (sum(datepart('h',FlightDuration)*60 + (datepart('n', FlightDuration))))

that should do it.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Vince, I've changed the direction of my prayer-mat.. It's in your direction (Joke!!)

Anyway, I tried it and it worked a treat thought upside down... I added DESC at the end and now all is just perfect...!

I owe you Vince..!

Cheers
Rich