denveromlp
Last Activity: Dec 3 '08, 06:25 PM
Joined: Aug 26 '08
Location:
-
EXIST function worked great, thanks! -
OK, so joining tables disallows you to complete a delete query.
I'm trying to delete records contained in the table "tbl_missingfle ets" from the table "tbl_missingroa dnummaster". Now I'm trying the following SQL, but it wants me to "specify the table containing the reconds I want to delete." How do I do that?
DELETE tbl_missingroad nummaster.ROAD_ NUMBER, tbl_missingroad nummaster.FLEET _NAME,...Leave a comment:
-
Error: "Operation must use an updatable query."
I've been getting the following error in a delete query.
This is the SQL:
DELETE tbl_missingroad nummaster.*
FROM qry_missingflee ts INNER JOIN (tbl_missingroa dnummaster INNER JOIN tbl_locodata ON tbl_missingroad nummaster.ROAD_ NUMBER = tbl_locodata.RO AD_NUMBER) ON qry_missingflee ts.FLEET_NAME = tbl_locodata.FL EET_NAME;
Ever run into this before?
Thanks! -
Figured it out...thanks to your guidance.
The make-table was passing the numerical month that was sorted ascendingly to the graph, but it was not selected in the row source of the graph, only the text month. Got it fixed.
Thanks for your help!Leave a comment:
-
Yeah, this was created in design view, then I just copied the SQL.
I'm concerned the problem lies in some setting the chart wizard uses that tells it to alphabetize any text field it graphs, the order seems to stay correct until that point.Leave a comment:
-
I'm not sure why it says SELECT in the SQL, it is definitely a successfully functioning make-table query, qry_graphbymont h generates tbl_graphbymont h.
No, totatOHDates is a seperate table that this query pulls raw dates from before counting the number of occurences in each month.
No, that SQL is just the a query that counts how many date data points fall within each month of the year. The year...Leave a comment:
-
...Code:SELECT Format(CDate(Month([Date_NextOH]) & '/21/2000'),'mmm') AS Monthtext, Count(Month([Date_NextOH])) AS OHMonth, Year([Date_NextOH]) AS Year INTO tbl_OHbymonth FROM tbl_totatOHDates GROUP BY Format(CDate(Month([Date_NextOH]) & '/21/2000'),'mmm'), Year([Date_NextOH]), Month([Date_NextOH]) HAVING (((Year([Date_NextOH]))=[Forms]![frm_Main]![tb_graphyear]))Leave a comment:
-
This is definitley a better way to convert month (1-12) into (Jan-Dec), but it still does the same thing as before. When the make table is generated, it successfully lists the months Jan - Dec. But when the chart is created from the table in a form, it still aranges the months on the x-axis alphabetically (apr-sep)Code:Format(CDate(intMonth & "/21/2000"), "mmm")
Leave a comment:
-
I think that is what I am doing in a round about way. I have a field for Month([DATE]) which returns values 1-12. That field is ordered ascending and is not shown. Then I have a field consisting of successive "IIF" statements to convert the Month([DATE]) into words.
Ex. IIF(Month([DATE])=1, "Jan", IIF(Month([DATE])=2, "Feb",.......II F(Month([DATE])=12, "Dec"
When I run the...Leave a comment:
-
Formatting Charts in a Form
Hello,
I have a form that creates a chart for me that displays the number of occurances (y-axis) by month (x-axis). The data is retrieved from a make-table. When the chart is created it plots the months on the x-axis in alphabetical order (Apr-Sep). How do I change it so it orders the months correctly (Jan-Dec).
(In the make-table, the data is listed properly from Jan to Dec)
Thanks! -
Code:SELECT DISTINCT tbl_MWHRSday.FLEET_ID, 1*(fCalculateMedian([FLEET_ID],[avg_mwhrs])) AS Median INTO tbl_fleetmedian FROM tbl_MWHRSday GROUP BY tbl_MWHRSday.FLEET_ID;
Also, here is the module fCalculateMedia n
...Code:Public Function fCalculateMedian(strfleetname As String, dblmotorMWHRS As Double) Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String Dim intNumOfRecords As Integer,
Last edited by denveromlp; Sep 5 '08, 02:37 PM. Reason: Fleet_name incorrect, should be Fleet_ID, same errorLeave a comment:
-
Aggregate Function Error Message
Hello,
I've been getting the following error message in multiple queries, over and over again and I don't understand what it doesn't like.
"You tried to execute a query that doesn't include the specific expression '1*fCalculateMe dian(tbl.MWHRSd ay.FLEET_ID,[avg_mwhrs])' as part of an aggregate function."
The '1*fCalculateMe dian(tbl.MWHRSd ay.FLETT_ID,[avg_mwhrs])' part is always something different... -
I'm getting a compliation error "User-defined data type not defined" on this line
rs As New ADODB.Recordset
is ADODB a add in that I may be missing? I'm using Access 97...Leave a comment:
-
1. Unit Number, Numeric
2. Download Date, Date/Time
3. MWHRS, Numeric
For each unit number, there are many download date's each with one measurement of MWHRS. For each unit, I need to find the minimum download date and associated MWHRS, and the maximum download date and associated MWHRS. Then calculate (MWHRS_2-MWHRS_1)/(DownloadDate_2-DownloadDate_1) .
The previous code took the maximum download date...Leave a comment:
-
Yup, that's it, just caught it myself.
Thanks for your help!...Leave a comment:
-
I'm not sure wha the purpose is, I based the code from http://bytes.com/forum/thread655107.ht ml
If I don't include it I get and error "Argument count mismatch"...Leave a comment:
-
OK, thanks for your help, I've got the module running, but it is returning incorrect values. In fact, the values it is returning as medians, are not even values included in the table. See any mistakes?
-Nate
Public Function fCalculateMedia n(strfleetname As String, dblmotorMWHRs As Double)
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
Dim intNumOfRecords As Integer, curMotorMWHR As Double...Leave a comment:
-
Median of Different Field Groups
Hello,
I have a table of variables (X1, X2, X3...) that each have multiple measurements (Y1.1, Y1.2, Y1.3, Y2.1...)
Variable Measurement
X1 Y1.1
X1 Y1.2
X1 Y1.3
X1 Y1.4
X2 Y2.1
X2 Y2.2
X2 ... -
ok, we're getting closer...thanks for your help with this
I pulled out all of the "WHERE Year(t.dteDate) =Year(Date())" clauses because the data in the table is already only data for the previous 12 months. Also, it is a rolling 12 months, not a calendar year, so some of the data is from the last part of 2007 and the rest from 2008. After removing where statements, seems to be running correctly.
The only problem...Leave a comment:
-
Looking to use days....Leave a comment:
No activity results to display
Leave a comment: