DDMS to Access Fill Cells Help!

[riccojs]

I currently have created a project and all is going well. I have
written a access program to retrieve data from a DDMS System (Supplier
program). The company has asked for several enhancements and I have
been able to handle them. There is over 250,000 records and the DDMS
system does not always populate all information.

Example:
Customer: Call # Call_SQ Tech Date Problem
Jim Jones 12345 100 John 01/01/2006 No light is
Jim Jones 12345 101 on the device
Jim Jones 12345 102 and no power.
Lee Harvey 54321 100 Jan 08/04/2006 Bad wheel and
Lee Harvey 54321 101 no hard drive.

I do not have much control over the information it is all done on a
text based system and I just pull the data out daily with a Maintenance
program that I wrote. In that program I clean all the records and
create nice clean tables. There is more columns then what I am showing
but this is a start.
In the maintenance program I run several different queries to organize
the data and the final product is a table with only the info that I
need and it is in order of Call# then Call_SQ. I have a Tech column
(Text), Three Date columns (Time/Date), and a number column. I would
like to place a line in the criteria box on one of my queries that
relates to one of the columns. To do what I am about to ask, I am not
sure if it is possible that way but I know you can do it with VBA I
just can't figure it out.
What I need is code that will look at the table (TBL_USER_TABLE) and
then at each of my 5 columns (Tech_Name, SA_C_DATE, SE_C_DATE,
SM_C_DATE, Just_A_Number). For Example "John" and "01/01/2006"
needs to be copied down the column until Access finds information
already in the cell, then copy "Jan" and "08/04/2006" down
until the next entry. This is so the above table will look like this:

Customer: Call # Call_SQ Tech Date Problem
Jim Jones 12345 100 John 01/01/2006 No light is
Jim Jones 12345 101 John 01/01/2006 on the device
Jim Jones 12345 102 John 01/01/2006 and no power.
Lee Harvey 54321 100 Jan 08/04/2006 Bad wheel and
Lee Harvey 54321 101 Jan 08/04/2006 no hard drive.

Remember I have over 250,000 records and I can do this at night with my
maintenance program, so proccessor time is not an issue and please
don't be angry about the table design and information the system is
very old and I didn't write it.

Thank you for the help and code to make this happen. I am using Access
2003 and my program is to look up information only not add information
to the system

Thanks Again
John

 

[Jeff Boyce]

From your description, it seems as if you are trying to create a
spreadsheet, in which the "duplicate" information is copied to all rows
(until there's a new Call#). While this might be how you'd have to approach
it if you were using a spreadsheet, this makes it much harder for you to use
the features and functions that Access offers.

Have you considered creating a well-normalized table structure and "parsing"
your incoming data into a structure Access can handle better? For example,
based on the data you provided, an alternate table structure might be:

tblPerson (I assume both your customers and your techs are "persons")
PersonID
FirstName
...

trelCall
CallNumber (if your Call# is a unique identifier)
CustomerID (a foreign key value pointing back at PersonID of the
Customer)
TechID (a foreign key value pointing back at PersonID of the Tech)
CallDate (date/time field)
Problem (a text field of 255 characters, if that is enough, or a memo
field, if not)

Since you are already parsing the data into your more spreadsheet-like
structure, it seems not all that difficult to put the incoming data into a
structure that Access can easily use.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[riccojs]

Jeff,

Thanks for the fast reply I agree with your thoughts but I don't have
that kind of control (I Think). The information comes from three tables
and all that i have listed comes from one table and the customer comes
from another. I found this code after serching for some time but I
still can't get it to work with what I have above. I'm not to sure
where to call the info and if it will even work can you modify with the
above info. I got the code from the forms but there is no author.
Thanks For The help.

Function CopyFieldRecords(pstrRST As String, pstrField As String,
pstrID As String) As Boolean

Dim db As Database
Dim rec As Recordset
Dim vCopyDown As Variant

CopyFieldRecords = True

On Error GoTo err_copyrecords

vCopyDown = Null

Set db = CurrentDb()

Set rec = db.OpenRecordset("select * from [" & pstrRST & "] order by ["
& pstrID & "]")

While Not rec.EOF
'If the field isn't blank then use this to copy down
If Nz(rec(pstrField), "") <> "" Then
vCopyDown = rec(pstrField)
Else
'Only if we have something to copy down
If Nz(vCopyDown, "") <> "" Then
rec.Edit
rec(pstrField) = vCopyDown
rec.Update
End If
End If
rec.MoveNext
Wend

exit_copyrecords:
Exit Function

err_copyrecords:
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
GoTo exit_copyrecords

End Function


'Call the function like this
pstrRST = "TBL_USER_TABLE"
pstrFieldToCopy = "Tech_Name"
pstrID = "IDField"
Call CopyFieldRecords(pstrRST, pstrFieldToCopy, pstrID)


If I can get the code to work I still am unable to call for the action
to change the fields. Thanks For the Help!
John

 

[Jeff Boyce]

see comments in-line below...

Jeff,

Thanks for the fast reply I agree with your thoughts but I don't have
that kind of control (I Think).

I must be missing something, then. It sounds like you are already receiving
the raw data and converting into the table structure you described in your
first post. If you are, you could be converting into a more well-normalized
structure instead ... you'd need to create Access tables and "load" them.

The information comes from three tables
and all that i have listed comes from one table and the customer comes
from another.

I don't understand what this means.

I found this code after serching for some time but I
still can't get it to work with what I have above. I'm not to sure
where to call the info and if it will even work can you modify with the
above info. I got the code from the forms but there is no author.

From what forms?

Thanks For The help.

Function CopyFieldRecords(pstrRST As String, pstrField As String,
pstrID As String) As Boolean

Dim db As Database
Dim rec As Recordset
Dim vCopyDown As Variant

CopyFieldRecords = True

On Error GoTo err_copyrecords

vCopyDown = Null

Set db = CurrentDb()

Set rec = db.OpenRecordset("select * from [" & pstrRST & "] order by ["
& pstrID & "]")

While Not rec.EOF
'If the field isn't blank then use this to copy down
If Nz(rec(pstrField), "") <> "" Then
vCopyDown = rec(pstrField)
Else
'Only if we have something to copy down
If Nz(vCopyDown, "") <> "" Then
rec.Edit
rec(pstrField) = vCopyDown
rec.Update
End If
End If
rec.MoveNext
Wend

exit_copyrecords:
Exit Function

err_copyrecords:
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
GoTo exit_copyrecords

End Function

Where do you have this code?

'Call the function like this
pstrRST = "TBL_USER_TABLE"
pstrFieldToCopy = "Tech_Name"
pstrID = "IDField"
Call CopyFieldRecords(pstrRST, pstrFieldToCopy, pstrID)

Where are you trying to run this code?

If I can get the code to work I still am unable to call for the action
to change the fields. Thanks For the Help!
John

We're not there, we can't see your database. How do you "get the code to
work" now? Where are you trying to "call for the action to change the
fields"? Change them from what to what? Where?

(much) more information, please!

Jeff Boyce
Microsoft Office/Access MVP

 

[riccojs]

Jeff,

Sorry a bit new to this. I do have a way to do the copy down and I will
list it but it will only work for one of my fields (Tech_Name) the
others would have to cross checked with call number and then paste only
if call number matches to first record in the seqence of calls.

Here is the whole picture. We have a system called DDMS it uses about
300 DBF files for its backend which are located in several different
folders. I have not been able to find the Key file that ties it all
together in DDMS. (Text Based system)

Out of all the files I only need four of them for the information that
I need, this is to make information available to sales who don't have
access to DDMS.

DDMS is old and the information is stored in ways that it souldn't be.
Management dosn't want me to touch these original files. I copy the 4
need files to a folder for me to work from.

I have designed a Maintenance program to organize the information and
it runs every morning after fresh copies of the DBF files are placed in
my working folder. In access I link to these 4 DBF files. (customer
info - 3000 records, machine info - 250000 records, cost - 3000
records, Misc - 5000 records). I have used relationships and there is
no key fields, customer info and machine info can only be linked via
customer number and the other two by system number to machine info
only.

The data in the DBF's is bad there is all kinds of place holders
(Spaces before information) and dates/times that are split into
multiple columns and several columns of data that I don't need. I run
several Queries to remove spaces, Dups, and combine dates and times
into there own fields. I do this with SQL and code Tech_Name is only a
number I created a table that relates the number to a name which I use
before the final table is completed, to convert number to name.

After my Maintenance program is done I have one table with 250,000
records that is linked to from the search program that I wrote. So I do
have a backend DB and a Front End user interface they can not enter or
change data. I have some other bells and whistles that are not relevant
to this.

The problem is most of the information comes from one table and in this
table is the service calls. DDMS uses around 25 characters for each
call in the "call problem" column and one service call could have
1000's of characters. That is why there is a call number for every line
and then a Sequence number for each line with the same call number. All
the information (Tech, Dates, Times, and some other info) is only on
the very first line of any given call and the rest of the sequence
numbers are blank for (Tech, Dates, Times, and some other information)

If the information was organized better in this one table with multiple
tables then I could easily fix the issues but it is in one Table (Not
the one I created but the original, the one I create is the above table
with all the other info integrated into it)

I know this is a big picture but I tried to tell you all I could. The
above code came from one of these Google groups not sure which one.

I found this code below and it does work. It does not do any checking
to to see if it belongs to that call. It just copies down until another
cell is found that is not blank. Now that I have tried it I really
can't use it without some checks involved because some calls don't have
any tech or date info and using the code below does add info to these
fields that should be blank.

'This is in a Module and called in a update query

Public Function SupplyMissingValue(V As Variant) As Variant

Static Stored As Variant

If Not IsNull(V) Then
Stored = V
End If

SupplyMissingValue = Stored

End Function



I may just end up dropping this but I would like to get it to work,
some thing like:

IF Tech is not null "copy" then get call number in current record, go
to next call number if = to last call number "paste" tech name in tech
field Loop until last record. Or something along those lines. I need to
do this for tech name and three date fields.

Your help is greatly appreciated and I do understand if you don't want
to help knowing now what I have to deal with.

Thanks
John

 

[Jeff Boyce]

See additional comments in-line below...

Jeff,

Sorry a bit new to this. I do have a way to do the copy down and I will
list it but it will only work for one of my fields (Tech_Name) the
others would have to cross checked with call number and then paste only
if call number matches to first record in the seqence of calls.

I'm not there. I don't know what you mean by "copy down".

Here is the whole picture. We have a system called DDMS it uses about
300 DBF files for its backend which are located in several different
folders. I have not been able to find the Key file that ties it all
together in DDMS. (Text Based system)

Out of all the files I only need four of them for the information that
I need, this is to make information available to sales who don't have
access to DDMS.

DDMS is old and the information is stored in ways that it souldn't be.
Management dosn't want me to touch these original files. I copy the 4
need files to a folder for me to work from.

I have designed a Maintenance program to organize the information and
it runs every morning after fresh copies of the DBF files are placed in
my working folder. In access I link to these 4 DBF files. (customer
info - 3000 records, machine info - 250000 records, cost - 3000
records, Misc - 5000 records). I have used relationships

?What does this mean? How have you used relationships? Where?

and there is
no key fields, customer info and machine info can only be linked via
customer number and the other two by system number to machine info
only.

A picture is worth a thousand words -- can you provide a table structure
that helps clarify what this means? For example:

tblCustomer
CustomerNumber
FirstName
...

tblMachine
MachineID
CustomerNumber
...

tblCost
SystemNumber
...

tblMisc
SystemNumber
...

The data in the DBF's is bad there is all kinds of place holders
(Spaces before information) and dates/times that are split into
multiple columns and several columns of data that I don't need.

Any chance that the import process can separate the input fields by
delimiters?

I run
several Queries to remove spaces, Dups, and combine dates and times
into there own fields. I do this with SQL and code Tech_Name is only a
number

?You have a field called "Tech_Name" that is a number?

I created a table that relates the number to a name which I use
before the final table is completed, to convert number to name.

Why the two-step process? How do you or Access know how to "relate the
number to a name"?

After my Maintenance program is done I have one table with 250,000
records that is linked to from the search program that I wrote.

Is this the table structure you described in your first post? If so, why
are you (re-)creating the un-normalized structure you receive in your
database?

So I do
have a backend DB and a Front End user interface they can not enter or
change data. I have some other bells and whistles that are not relevant
to this.

The problem is most of the information comes from one table and in this
table is the service calls.

None of the four tables you described earlier was named "service calls"...
where is this information coming from?

DDMS uses around 25 characters for each
call in the "call problem" column and one service call could have
1000's of characters. That is why there is a call number for every line
and then a Sequence number for each line with the same call number.

I get this part from the data example you provided. But you are NOT
required to duplicate a poor structure in your read-only database you are
building for lookup.

All
the information (Tech, Dates, Times, and some other info) is only on
the very first line of any given call and the rest of the sequence
numbers are blank for (Tech, Dates, Times, and some other information)

If the information was organized better in this one table with multiple
tables

I don't understand what you mean by this.

then I could easily fix the issues but it is in one Table (Not
the one I created but the original, the one I create is the above table
with all the other info integrated into it)

Now I'm even more confused -- can you "draw" a map explaining the pieces and
how you get from one to the next?

I know this is a big picture but I tried to tell you all I could. The
above code

No code above...

came from one of these Google groups not sure which one.

I found this code below and it does work.

What does "work" mean to you here?

It does not do any checking
to to see if it belongs to that call.

Again, we're not there, we can't see what you're seeing. "...to see if it
belongs to that call" means nothing when we don't know what "it" and "that"
refer to.

It just copies down

What do you mean by "copies down"?

until another
cell is found that is not blank.

Not there, can't see ... what "blank"? What "cell"?

Now that I have tried it I really
can't use it without some checks involved because some calls don't have
any tech or date info and using the code below does add info to these
fields that should be blank.

What "checks"? Why should the fields be blank? ?!What fields?!

'This is in a Module and called in a update query

Public Function SupplyMissingValue(V As Variant) As Variant

Static Stored As Variant

If Not IsNull(V) Then
Stored = V
End If

SupplyMissingValue = Stored

End Function



I may just end up dropping this but I would like to get it to work,
some thing like:

IF Tech is not null "copy" then get call number in current record, go
to next call number if = to last call number "paste" tech name in tech
field Loop until last record. Or something along those lines. I need to
do this for tech name and three date fields.

Sorry if I'm seeming dense, but I'm just not seeing enough information to
offer what I would consider meaningful and useful suggestions.

Your help is greatly appreciated and I do understand if you don't want
to help knowing now what I have to deal with.

You may want to consider finding a student who could take on this project as
part of a school assignment, or perhaps someone who can offer further
assistance on a consulting basis. This seems to be a bit bigger than a
breadbox...<g>

Best of luck on your project

Regards

Jeff Boyce
Microsoft Office/Access MVP