I am trying to find a solution to the following problem. First off
I'm new to Access and SQL but have a good back ground in other
languages, thus at a new job this has just been dumped on me so as is
my style I agreed figuring that over time it's just another language
and style anyway to add to the others I've worked in. Heres my
situation.
Have used a make table query to establish a Temp historical archive
table for reporting variances in certain financials.
Table is made each time going back 14 months. Problem that has been
encountered is the records contain a large variety of gaps.
IE archiving the month and year is all that is availiable so I get
months 2,3,4,,9.10,11, 12 for 2004 and 1,2,3 for 2005 in some cases.
but the gaps all vary.
What I want to do is useing separate month and year fields look at each
id (in this case its apartment #'s and see what months are missing and
fill in the blanks with the previous months data and insert the missing
month and corresponding year value.
heres a sample of a record set that may illustrate what I am trying to
say.
PID Apt ID Full Name Type Read Year Month Date
ha ha-820505 Benis, Bill Gas (Heat) 0 2004 9 27-Jul-04
HA ha-820505 Benis, Bill Gas (Heat) 0 2004 10 27-Aug-04
HA ha-820505 Benis, Bill Gas (Heat) 0 2004 11 27-Sep-04
HA HA-820505 Benis, Bill Gas (Heat) 6169 2004 12 26-Nov-04
HA HA-820505 Benis, Bill Gas (Heat) 11211 2005 1 30-Dec-04
HA HA-820505 Benis, Bill Gas (Heat) 20571 2005 2 28-Jan-05
HA HA-820505 Benis, Bill Gas (Heat) 26585 2005 3 02-Mar-05
ha ha-820506 Harris, Ja Gas (Heat) 0 2004 9 27-Jul-04
HA ha-820506 Harris, Ja Gas (Heat) 0 2004 10 27-Aug-04
HA ha-820506 Harris, Ja Gas (Heat) 0 2004 11 27-Sep-04
HA HA-820506 Harris, Ja Gas (Heat) 141 2004 12 26-Nov-04
HA HA-820506 Harris, Ja Gas (Heat) 141 2005 1 30-Dec-04
HA HA-820506 Harris, Ja Gas (Heat) 141 2005 2 28-Jan-05
HA HA-820506 Harris, Ja Gas (Heat) 154 2005 3 02-Mar-05
For any missing month and corresponding year I would want to add the
pervious months values and add the proper month and year to fill the
gaps. The Read date would be set to a text value of none to qualify
the record as a fabricated record.
This is just a first step in a very long process.
Does anyone have any ideas at all for how they might attempt this.
Thanks
Rob
1  1728 
Sorry .. heres am more appropriate example of the record sets I'm
trying to resolve. My previous example was bad because that property
set wasn't a year old so it's records were mostly in order.
Sorry about that
Property ID Apt ID Full Name Type Read Value Year Month Read Date
AR AR-428-1A Coronado, Jose Gas (Heat) 346024 2004 2 1-17-2004
AR AR-428-1A Coronado, Jose Gas (Heat) 367238 2004 3 2-17-2004
AR AR-428-1A Coronado, Jose Gas (Heat) 384589 2004 4 3-17-2004
AR AR-428-1A Coronado, Jose Gas (Heat) 392711 2004 5 4-17-2004
AR AR-428-1A Coronado, Jose Gas (Heat) 392711 2004 6 5-17-2004
AR AR-428-1A Salinas, Marisela Gas (Heat) 393069 2004 12 11-16-2004
AR AR-428-1A Salinas, Marisela Gas (Heat) 394422 2005 1 12-16-2004
AR AR-428-1A Salinas, Marisela Gas (Heat) 401970 2005 2 1-16-2005
AR ar-428-1A Salinas, Marisela Gas (Heat) 410442 2005 3 2-18-2005
AR AR-428-1D Sanchez, Adrian Gas (Heat) 182920 2004 2 1-17-2004
AR AR-428-1D Sanchez, Adrian Gas (Heat) 217920 2004 3 2-17-2004
AR AR-428-1D Sanchez, Adrian Gas (Heat) 227471 2004 4 3-17-2004
AR AR-428-1D Sanchez, Adrian Gas (Heat) 237997 2004 5 4-17-2004
AR AR-428-1D Sanchez, Adrian Gas (Heat) 237997 2004 6 5-17-2004
AR AR-428-1D Sanchez, Adrian Gas (Heat) 281410 2004 12 11-16-2004
AR AR-428-1D Sanchez, Adrian Gas (Heat) 323720 2005 1 12-16-2004
AR AR-428-1D Sanchez, Adrian Gas (Heat) 356110 2005 2 1-16-2005
AR ar-428-1D Sanchez, Adrian Gas (Heat) 392050 2005 3 2-18-2005
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: David |
last post by:
Hi,
I have an order form which has a field 'ProductID'.
This form has a button on each record to open a new form linked by
ProductID.
This new form is a continuous form and obviously, only displays the
records associated with that ProductID.
Basically this is part of a Product Packing Pick System. From my
products table, each product has a set default amount of items which
|
by: Keith |
last post by:
There are certain records I create which are open and shut. That is,
we don't put a completion date in that record. I would like to
automate this and with certain records, take the date created field
and place that date in the completion field. Any suggestions?
|
by: Carl |
last post by:
Hi,
I have a query that produces an output similar to the sample shown
below. These records are based on certain criteria and a date range
selected from a form. I need to create some kind of calcualtion or
record manipulation to do the following:
I need to subtract dates from one another, for example for ID 1 I want
to find the difference between 24-Jun-04 and 25-Jan-06 for the 2
applications for Person1.
|
by: Ron |
last post by:
Hi All,
Using Access2000, winXP.
Table 1 = tblClients displayed on frmClients via qryClients. 2nd table =
tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails
shown on subform(to frmInvoices) sfrmDetails via qryDetails.
Relationship built between tblClients/tblInvoices/tblDetails by ClientID.
Relationship between tblInvoices/tblDetails by InvoiceID. All works fine if
|
by: Kosmos |
last post by:
Alright so I've got this Outlook code written in VBA in Access. The first part, which works, records information about appointment times based on the required days before notification of certain contracts and then it adds them to the outlook calendar of the current user. This code works and is nested within a bunch of if statements because it only needs to trap certain appointments. The table I create with this code is later used to attempt to...
| | |
by: Bhujanga |
last post by:
I have some reports whose purpose is to show whether any records currently meet certain criteria, so of course the report is based on a query where that criteria is defined. If there don't happen to be any records that satisfy the criteria at a particular time then the report produces with one line in the detail section that says "#Error". This confuses the users becuase they think there is something wrong with the report. Is there a way to...
|
by: Soccer5 |
last post by:
Trying to Count records on a report that meet a certain criteria.
Have a text box in the Report Footer that has the following in the Control Source: =Count(="S")
This does not work. It counts ALL records whether Record Type = "S" or "M"
There is nothing unique about the records to distinguish S-type records from the M-type records.
|
by: mfaisalwarraich |
last post by:
Hi Everybody,
I am using the following code to get the recordset of an external database.
Dim dbPatients As Database
Dim rsCountPatients As Recordset ' to count number of patients in a table
Const strDBPath = "C:\Patients.mdb" ' external database
Set dbPatients = OpenDatabase(strDBPath, False, True, ";PWD=abc")
|
by: dlevene |
last post by:
Hi - newbie here, be gentle. In Access 2003, I've created a report (based on a query) to produce mailing labels for all records where = "Community". is a combo-box field with the values coming from a seperate table.
Problem is, some of these don't have addresses (ie. , and are null). So I want to use the address of the Administrator for that community. This information is in records where = "Administrator" and = whatever the community...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
