Review - Table Design - Meter Reading / Adjustments
Aug 3, 2005
I am working on a database to keep track of meter readings from several meters. Am at the table design stage and have come up with two seperate table designs but am wondering what would be best.
Each individual 'Meter' tracks more than one 'Fuel'. All of the regular meter readings should occur on the last day of the the month. The difference between the current month's reading and the reading from the previous month is the usage for the current month.
Though rare, there are occasions when there could be adjustments to the meter. A meter may be reset to 0 or to any other number due to a number of issues (replacement, maintenance).
The usage for a Meter-Fuel combination calculated by the current meter reading & previuos meter reading is assigned to current readings' date, As readings should onlt be taken at the end of the month, all other readings should be adjustments ONLY!
I think that the second version is the way to go, since I would still have to create a query to join data from both the reading and adjustment tables in the first design that would look like the data above.
Here what I consider to be the drawbacks to either table structure
Version 1
The first version requires more complicated queries to calculate the difference between meter readings.
Version 2
The second version will require some coding/querying to make sure that there are always two adjustment records for one date and determining which is the pre-adjustment and post-adjustment meter reading since it may not be true that the meter is reset to zero; it may even be set at number higher than the pre-adjectment reading.
Any thoughts on which design is better? Or any problems I could run into later with designing forms/queries/reports that perhaps I have not thought through?
I have a table full of meter readings. I want to have a field called consumption which looks at the reading just entered for that month and then subtracts the previous months reading for that meter which leaves the consumption.
Can I get a formula that can work this out automatically?
I found this from different fourm by searching google. This is exactly what I need to do and the answer was given; however, I can't figure it out. Can someone please post a step by step instruction on how to complete the following.
Thank you!
Question
I have a datbase full of projects that must be ranked in priority order. This was simple enough but when I add a project I will likely need to change the priority rank of all the "lesser" projects. With over 500 projects adding a new project with a rank of 50 means changing all the ranks 50 and above. I want to be able to add a project and when I determine its rank I insert the rank number and every rank equal to the project just added is the indexed by 1 digit. No two project can be ranks the same, one is always more important than another.
Answer
Create an update query that sets the priority field to:
Hi, I need to grab a record from an input table/record set and de-concatenate it based on the underscore and append each instance to an output table. I know this must be possilbe, we do simular things in GIS programming all the time. Below is my "logic"/psuedo code :)
'loop until end of file strColumnA = tblNewRelationships.PLACE strColumnBin = tblNewRelationships.NAMES 'loop until strColumnBout is "" strColumnBout = Left(strColumnBin, Find("_", strColumnBin, 1) - 1) strColumnBchop = Right(strColumnBin, Len(strColumnBin) - Len(strColumnBout) - 1) strSQL = "" 'put strColumnA into tblRelationships_2.PLACE and strColumnBout into tblNewRelationships.NAME DoCmd.RunSQL strSQL strColumnBin = strColumnBchop 'end field loop 'end row loop
I have two options on the main switchboard page and when I click on the second option it goes to a secondary page that gives me 3 options with the third option being to return to the main screen. When I select that option it does go back to the main screen but then on the main screen it shows the return to main screen option as well and when I select it.... it says " There was an error reading the switchboard items table." But I want the main screen to only show the original two options only.
I am trying to use a Connection to an xlsx spredsheet to read in a table of information (the data is not a table, just laid out in a table, see attached, the one I am trying to load is xlsx not xls but I cannot for some reson upload the xlsx one on here)
I have the following declared:
Code: Dim cn As ADODB.Connection Dim rs As ADODB.Recordset
The Microsoft Access database engine could not find the object 'table$'. make sure the object exists and that you spell its name and the path name correctly.....
I did a Debug.print to check the strConnectionString was correct and the Source is exactly correct and refers to the name of the file I am trying to access exactly.
Hi, I am new to Access Forum and I have a very easy question: I need to show a Progress Meter in a Form whilst my Dbase is running a Macro containing several Queries - these Queries are Delete, Append etc and finally a MsgBox.
Any ideas on How to do a Progess meter whilst the Macro is running??
I have a form that, when I press a button it loads another form. The second form takes about 20-25 seconds to load due to the fact that the (split)DB is running over a network, and the form itself has many fields on it. I'm thinking that my users will think that their computer is locked up and keep closing out of the DB. I would like to add a progress meter or percent meter so the user will at least see that something is happening. Has anyone ever programmed one of these things, and if so, could you please give me some insight? Thanks.
I m new, and i want to make a meter reader in Access for daily use. i want to be enter only last reading every day at data entry form and that last reading must appear as first reading of the next day automaticaly in same table, because i have only one table in my database. fields are "date", "1streading", "2ndreading" & one data entry form and one report. is it possible in access, if so please help me. thanks
I use syscmd(acsyscmdInitMeter) and syscmd(acSysCmdUpdateMeter) frequently. I noticed that the acSysCmdUpdateMeter has two arguments. I know the first one updates the counter, but I have not found any documentation for the second. Does anyone know how it can be used?
I would like to modify the Status Bar description that is set in the InitMeter action with the UpdateMeter action. Is there any way to do this? For example:
I have a form that takes sever seconds to load, due in part to the number of records I suppose, however I would like to create a progress meter (form?) that will run while the form is loading. I have tried several different suggestions but none work. Can anyone help me?
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
Here is the code that I was thinking of, I don't think its right but it might be a start.
Dim Count As Variant Dim CountImplementedTotal As Variant
CountCost = 0 CountImplementedTotal = 0
Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordSet("ECM Details") Do While Not rst.EOF If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then If rst![Has Measure been Selected] = "YES" Then Count = Count + rst![Total Measure Cost] End If End If rst.MoveNext Loop
Hi I have a database for maintaining procurement contracts which includes a start date for each contract and a frequency of reviews required (monthly, qrtly, bi-annual, yearly). I need to be able to generate a report that will tell me when the next review is due for each contract, taking today's date into consideration.
This is the setup so far:
tblRegistry - contains field 'Commence' for the start date of the contract and 'Review Req' to indicate how often a review is required (monthly, yearly etc).
tblReviewReq - contains field 'Review Frequency' (monthly, yearly etc) and 'Days' to assign the number of days eg monthly = 30, yearly = 365 etc.
Next I have a query that includes the above tables using the fields 'Commence' and 'Days' and have created a calculated field to give me the next review date ie contract start is 1/08/07, review freq is monthly (=30 days), therefore the next review is 1/09/07. "NextReview:[Commence]+[Days]"
But how do I get it to tell me the next review date after the 1/09/07? Ultimately I'd like to be able to pull a report at any stage which will tell me all the reviews that are coming up.
I have built an access database (Access 2002) which contains around 370 records.
It would be useful to be able to click a button on a form to review which records (forms) have been amended or what records are new.
This could be presented by either (1) sorting the records so the newest/or latest changed records came first, followed by the next most recent etc etc, or by (2) presenting a list of record names which could be followed manually.
I have tried several text books but have been unable to find a solution so any help from the experts would be very welcome.
I have one Query with the following: Main.ID, Main.First Name, Main.Last Name, Main.Assigned Date
I created a Daily Report using this Query to tell me when I need to meet again with these people.
I have a Report that has the following: Main.ID, Main.First Name, Main. Last Name, Main.Assigned Date, [Assigned Date]+30,[Assigned Date]+60,[Assigned Date]+90,[Assigned Date]+120,[Assigned Date]+150,[Assigned Date]+180
What I would like to do:
The current report shows ALL records with the requested information. How can I have the Query or Report filter out the records that do not have any of the review dates falling on today's date?
I hope I explained myself. If further explanation is necessary let me know.
This database is for a car event that's held once a year. It has been a work in progress for about 15 weeks now and is basically finished. There is only a couple of errors and not sure where to start. The database is so big it is getting a little out of reach for me to organize everything.
Idea of the database is that I can enter entrant's information, there car details and any merchandise they want.
Main errors I’ve noticed are;
In email form if you select one user it still emails everyone. In the car details form the induction box does not show the value list I created in the car table. when entering a t-shirt in the products form you select the shirt size. Is this the best way to lay this out. In the Order Form what is the best way to add a freight box between Sub-Total & Total where you can type the freight in and it's automatically added to the Total Box
Have a look over the db and let me know what ya's think.
I'm having some problems with VBA code I'm writing that references a subform. I'm pretty new to VBA and am not sure what the problem could be. The parent form (TrackingForm) tracks dates different tasks were completed. The subform (Child2) tracks exceptions that also need to be marked completed. I need ALL dates to be marked as completed before the entire tracking record can be marked as completed.
I have the following code entered into the OnFocus event of the tracking records Me.Completed_Date field:
Private Sub Completed_Date_GotFocus() If IsNull(Closing_Date) Or IsNull(Package_Received) _ Or IsNull(Upload_By) Or IsNull(Initial_Review_Date) _ Or IsNull(Me.Child2.Form![Date Exception Completed]) Then Me.Completed_Date.Locked = True MsgBox "Completed Date cannot be entered - outstanding items.", vbOKOnly, "Warning!" Else: Me.Completed_Date.Locked = False End If End Sub
What ends up happening is that, if there is more than one exception record on Child2, the code only seems to care if one of the records (usually the first record) has something entered in the [Date Exception Completed] field. I can't seem to make it look at any successive records in Child2 to ensure they are also completed prior to unlocking the Me.Date_Completed field. I have googled this extensively over the last day and the only response that seems to recur involves using a Recordset function that I have never used and am unsure how to.
I've got a database that allows all users to review an address register. If they find errors or mostly get updated information they have another form they can fill out to make the recommended changes. Only I have access to the main DB to make the change permanent.
The question I have is there a way for Access to send me a notification that someone has requested the change?
I know that sending an email is doable - in Access. However, that's not an option for our network here. I was thinking something along the command prompt "Net Send" command. However I've not been able to get that to work by itself - let alone within Access...
I have used VB code in the past to store what PC is using the database, I could modify that to alert me whenever I open the DB up from my PC. But I was trying to get away from having to open this particular DB up every day and check for changes.
I am designing a database for my organisation. I have done most of it but am stuck on this. Ideally I would like to have a check box (in a table) that when checked a load of data is carried across from that record to a record in another table.
Given that I can't and don;t want to use VB is there anyway that this can be done easily?
This only shows the flow if the main category is "Floors", sub category "Joist", and then the choices under subcategory "Joist". There will be others for the other categories, subcategories, and choices.
Can someone help me or steer me in the right direction in setting up the database table/tables to accomplish this. Oh and BTW, there will be prices attached to each "Choice" under the subcategories.
I hope that this makes sense... any help would be much appreciated.
