help writing a macro

[brokerluke]

need help writing something that will do something similar to this which i
can do manually.

find / replace ie

find the word contract and erase it in the entire database
i need this to find multiple words ie.
find contract erase contract
find data erase data
find number erase number

 

[Jeff Boyce]

You've described "how", as in how you are trying to accomplish something.

If you'll describe more about the "why" (what will having the ability to
erase/replace words in your database allow you/your users to accomplish),
folks here may be able to offer alternate approaches.

It does seem like a fairly atypical database design that requires manually
(or via macro) finding/replacing...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[brokerluke]

the data i import into my database comes a little messed up.. i get certain
cells that have

contract:bob as the CELL i want to get rid of the contract: and just have
the name left

so like
contract:bob
contract:amy
contract:bill

i do a findall and replace each time i import new records.. i'm wondering
how to automate this to run 8 different find / replaces automatically

 

[Jeff Boyce]

I still can't tell if what you are doing is taking raw data (?from a
spreadsheet?) and putting it directly into Access.

Access is a relational database, and the features and functions it offers
work best if you feed it well-normalized data. Usually, spreadsheet data is
NOT!

Have you looked into importing the data as-is, and using queries to parse
the raw import data into more permanent (*and well-normalized*) Access
tables? You could use this approach to both normalize the data and strip
out the unneeded portions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[brokerluke]

yes it seems you are talking about what i'm trying to accomplish i get data
that i import into cells that sometimes has things i dont want.. like
contract:bob whereas i just want bob to be in the cell...

or even like i'll have some cells that say DS1/T1services:
like some cells are merging certain data i dont want in there i mean its
readable but when i try to organize everything i have problems...

how do you run the quaries to seperate out the data and make it import the
way you want it?

the problem is my data comes from txt files that aren't dilimitted properly
because i pull them off cut n pastes from varies websites then use a program
to kinda half-ass delimit the files thats why it turns out this way.. any
suggestions?

 

[brokerluke]

maybe i could email you what my data looks like from my txt file and you
could tell me the best way to parse it into a table?

here is EXACTLY what the data looks like before i try to convert it into a
delimitted file to import into access with my program. i'm only interested
in the values that follow the :'s but i want them seperate like without the
Customer Name: client
i want it just
client
is this possible?

Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

 

[brokerluke]

also.. i've had success in importing the files directly into access my ONLY
problem is i have no way to seperate each record.. it just lists everything
in 2 fields just like this... is there a way to differentiate between
records?

Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1



Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

 

[Jeff Boyce]

I'm having trouble visualizing the raw data you are importing, and the table
structure to which you wish to "parse" it. More info, please...

To offer suggestions on "how", I need to know "from where" and "to where"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[brokerluke]

this is what my data in my txt files looks like...
i need to parse out the data somehow into my table.. my table columbs are
headed
customer name
contract file number
status
master BTN..
etc...etc...

so i need to figure out how to get the values to the right of the colons)
to parse into the rows AND also differentiate between different records
i assume i can use the Contract Information: line to begin a new record to
parse?




Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

Contract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

 

[Jeff Boyce]

Are you saying that the "records" you receive are actually multiple lines
long, with one "field", a colon, and a value, repeated multiple times?

I looked over the data you described below, and see one set that looks like
"contract info", a second set that appears to be the same "contract info"
(but more limited), then third and fourth sets that have other kinds of
information.

I don't understand what a "Customer Contact TN" is, I can't tell how the
data is related, I still don't think I have a clear enough picture of what
you are trying to map from and to.

Are you saying that any and every item that is followed by a colon in your
input needs to go into a field/column in your Access table(s)?

Access is a relational database. Just because the data comes in the text
file as you've described doesn't mean it's a good idea to stuff it into
corresponding fields in a (single) Access table.

What are the entities (things about which you wish to store data) and
relationships (how are the entities inter-related)? You need to know that
before you build the Access tables, then you need to figure out how to map
from what you have into this (?new) Access table structure.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[brokerluke]

the data to the right of the colon ) goes into fields for the columns.
each row should be a record everything to the right of the colon is data
about the record.

numbers/dates/figures
i want it to look something like this

|company|client|number|MARC|service|product

the data to the right of the colons will go into those fields...

 

[brokerluke]

http://i264.photobucket.com/albums/ii173/penatrode/DBpic.jpg

here is the link to what my database looks like after import

 

[Jeff Boyce]

Please re-read my last response.

The form in which you receive the data is NOT the form in which you
necessarily need to store the data. In fact, in many cases, both you and
Access will have to work overtime to make sense of data in its original
(import) form.

That's why you need to spend the time normalizing your data structure before
then using queries to load from the input to the more permanent, well
normalized data structure.

I'm guessing that the raw data is on multiple lines.

If that's the case, you'll need to create a procedure that explicitly cycles
through each "line", deciding whether it has a new "record" or the
continuation of an old one, and to where the data contained needs to be sent
(see above description of 'normalizing').

If this description doesn't make much sense, plan to either spend more time
working your way up the learning curve on these topics or finding someone
who can work directly with you to build what you specify. Folks here in the
newsgroup are volunteers, so you may not find someone with enough time to do
this on a volunteer basis -- that is, you may need to look for someone you
can hire to help you get this done.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP