Search results

Table Motor MotorID, MotorType, FuelType, Contact, Dept AutoNum, word, word, word, word Table MotorRecord MotorID, RecordDate, Amount AutoNum, Date, Num Table MotorType MotorType, FuelType, CO2 word, word, Num SELECT Motor.Dept & " " & Motor.MotorType & " " & Motor.FuelType AS Expr1...

Table RecycleRecord ItemName, RecordDate, Amount Paper, 1/7/2013, 100 Table RecycleItem ItemName, UnitInKg Paper 1 Table PaperType PaperType, KgPerUnit A4 Paper, 2.5 Table PaperUsage PaperType, RecordDate, Amount A4 Paper, 2/7/2013, 100 I want results like...

Table ElectricityUsage UserID, RecordDate, Amount AutoNum, Date, Number I create a blank report and add two text boxes with control source =[ElectricityUsage]![RecordDate] and =[ElectricityUsage]![Amount]. Even only with the two boxes, the error appears, #name Then I tried another method. Add...

I tried and made correction. new qryFuzzyYearMatch SELECT DateSerial(Year([electricityUsage].[Occurrence]),1,1) AS UsageYear, Max(ElectricityEmissionFactor.Occurrence) AS MaxVanOccurrence FROM ElectricityUsage, ElectricityEmissionFactor WHERE...

I would like to do that as well as i think that would be a relational database supposed to be. But I can't control the data.

Just find out i missed to state one important point The query #12 works fine in grouping by academic year. But if you look at my query at first: Sum(ElectricityUsage.amount*(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor WHERE...

Thanks for the supportive comment. But a issue is the wrongly referencing electricityUsage.amount to ElectricityEmissionFactor.co2emission. Sum([amount]*[co2emission]) AS Expr3 For example amount within JUL/2010 to JUN/2011 is multiplied by co2emission of 2010. The intended way is amount within...

sorry, i really didn't express myself well. Let me re deliver the problem. ElectricityUsage: UserID Time Amount 1 1/7/2010 23230 1 8/10/2011 34340 1 8/1/2012 34300 1 2/3/2012 43430 1 4/2/2013 43560 1 3/2/2014 44540 2...

I want to have something like this: SELECT ElectricityUser.UserName, Year(ElectricityUsage.[RecordDate]) AS [Academic Year], Round(Sum(ElectricityUsage.amount* (SELECT TOP 1 CO2Emission from ElectricityEmissionFactor WHERE year(ElectricityEmissionFactor.[FactorStartDate]) <=...

Let say ElectricityEmissionFactor Occurrence Emission factor 1/1/2011 0.1 1/1/2012 0.2 ElectricityUsage Occurrence Amount 1/3/2012 10000 In this case, 10000 should be multiplied by 0.2 since it is the factor for the year. But to show the data in academic...

Thanks for giving such a important key word. Considering doing it in a query only, is it possible? Tried using Year(DateAdd("m", -6, ElectricityUsage.[RecordDate])) to change the date. But the problem is ElectricityUsage.amount*CO2Factor must be correctly referenced by the same year. A...

[solved]how to group by academic/user-defined year This is further application on: htt p://www[dot]access-programmers[dot]co[dot]uk/forums/showthread.php?t=279458 A quicker look, here is a query: SELECT ElectricityUser.UserName, Year(ElectricityUsage.[Occurrence]) AS [Year]...

It works fine thanks but I don't understand the mechanism. As ElectricityUsage.Occurrence => Occurrence works, it seems to mean ElectricityUsage.Occurrence and Occurrence refer to two different stuff. Then, what does Occurence refer to?

O... I find that. sorry for a dummy question. query>Design>create table

thanks for the result. And second with spikepl, why does it happen? In my mind, three columns to be drawn to a chart, that seems to be ok. Also with one question, did not see any delete or update in the SQL, how can it do so / where to see the settings?

lesson learnt I double checked the words and there is no reserved word. And wounder why "time" works fine before for table entry and query as a reserved word?

thanks spikepl for replying It seems "time" is not a reserved word because i did not get any message regarding reserved word when i made it a field name. I did get the message when trying to use "date". Whatever i tried your suggestion by changing that to "Occurrence" but it did not work...

hi, all a newbie to access currently trying to develop a database