473,320 Members | 1,977 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Adding missing records based on certain criteria

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

Nov 13 '05 #1
1 1704
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

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
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...
2
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...
2
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...
7
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...
2
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...
1
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...
5
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...
2
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...
2
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.