Solved

importing old DBF files

Posted on 2001-06-14
13
267 Views
Last Modified: 2011-09-20
What's my best route? I have an old dbf file. The transaction table has a link field set up as follows:

Customer No.   Date    Item
1              6/15/01  d8fj98

now, this is kinda like a reciept for two items.
Item d8f is one item and item j98 is another. My problem is that I am creating a new database and do not want to loose this info. Any suggestions.

The way I set up databases would be to have a table that would link by a transaction number and have two entries.

0
Comment
Question by:RKeliher
  • 7
  • 4
  • 2
13 Comments
 
LVL 1

Expert Comment

by:underground712
Comment Utility
You will get your exact table stucture  from the dbase, you will have to set up your relationships,if you have more then one table.just creat a new acess Db ,File>
get external data > Import.  pick the file location, and in the files of type down the bottom, pick dbase(dbf)
most likely it will be version 5, or if you know pick the correct version, if not start with the newest (highest #) and work your way back !
Is this what you are asking ?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Import the table as is first, then setup a new table and split the records the way they should be.

You should be able to import the DBF directly.  If not, first save the DBF file (with your old app) as a text file, then import to Access.

If you only have the file, feel free to send it along and I'll get it imported for you.  I have all versions of Access plus a varity of tools that I can use (like Data Junction).

Jim.
0
 
LVL 1

Expert Comment

by:underground712
Comment Utility
if you have one table called Item,create two new fields Item1   and   Item2 , make a new query and hit design view,just close show table , go to view menu and pick SQL and paste this
 
UPDATE tblTransaction SET tblTransaction.Item1 = Left([Item],3), tblTransaction.Item2 = Right([Item],3);
 
this is set up for Item,Item1,Item2, if table names vary change here,item has data, and the other will be your new fields,when done you can delete item
  In query, hit view and change back to design view
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
undergorund712,

"if you have one table called Item,create two new fields Item1   and   Item2 , make a new query and hit
design view,just close show table , go to view menu and pick SQL and paste this"

 That would result in a bad design because you would be breaking some normalization rules.  They should be seperate records if they represent two different things.

Jim.

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
I probably should add to bit and say that this is always what kills some older conversion efforts.  When you move from a non-relational to a relational DBMS, the design must change.  Data just can't be stored the same way.

Jim.
0
 

Author Comment

by:RKeliher
Comment Utility
I have several DBF files. One is customers, one is items, one is transactions.
The orginal creator tried to make it relational, but stored the info in the transaction file improperly. I'm trying to figure out a way to fix it so that I can keep all data in the program I'm trying to put together.
The data in the transaction table takes the first three letters (he did not use numbers) of each item ID and puts them all together as seen in the example above. It also does the same with the price field. Here's what it looks like
(this is in one record)
Item - om2ifc
Price -  4500 2000

I noticed that he put a space inbetween the prices but not in the item. Although if the item id is only 1 or 2 letters, then it looks like this
i1 ind
There is a space representing the third letter.

Do either of you know how I might fix this?

I would like the layout to be as follows
Transaction ID
Item
Price

Then there would be two records, but they would each have the same Transaction ID.

Thank you
RKeliher
Please let me know if this sounds confusing, I can send a portion of the database over if needed.

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Import the DBF tables as is.  Setup new tables as you need.  Then execute two append queries, which use the DBF table as input and your new tables for output.

The first query would set the item field as underground indicated:

Left([Item],3)

and the cost with:

Left([price],instr([Price]," ")-1)

second query would do:

Mid([Item],4,3)

for item and

Mid([Price],instr([Price]," ")+1)

Jim.


0
 

Author Comment

by:RKeliher
Comment Utility
Would this work if there are 9 or 12 items?
Everything in the item field is divided by 3
RKeliher
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Yuck...

  Yes it would work.  You'd just have to adjust the query each time to look in the right place.  Sounds like it would be better to use code though to do this.  VBA now has a Split function which takes a string and breaks it up into parts based on some delimiter.  In your case, this would be a space.

But what I wondering is if you have any item descriptions that are more then one word<g>.

Jim.
0
 

Author Comment

by:RKeliher
Comment Utility
Jim,
The least amount of an item code is 3 letters or numbers, the most I have found is 12.
I really don't mind doing the queries. I'm not familiar enough with code at this point.
Can you help me to take the item code field and append the first three letters or numbers to a new field called item1, then run another query to take the second three letters or numbers and append them to a field called item2, then do the same with the next set of letters and numbers, etc.

RKeliher
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
Comment Utility
<<Can you help me to take the item code field and append the first three letters or numbers to a new field
called item1, then run another query to take the second three letters or numbers and append them to
a field called item2, then do the same with the next set of letters and numbers, etc.
>>

 As I pointed out earlier, it is wrong from a relational point of view to have "Item 1, Item 2, Item 3" all in one record.  They should be seperate records.

Your table structure will be this:

Customer
Date  (rename this to something else - 'DATE' is a reserved word)
Item
Price

First import the DBF table as is.

Next build a select query against the imported table. Select the customer and date fields and place them in the grid.  For the Item column, enter:

NewItem: Mid([Item],1,3)
NewPrice: GetToken([Price],1)

GetToken() is the following function that you need to paste in to a module:

Function GetToken(strParam As String, intTokenNumber As Integer) As String

  Dim aStrings() As String

  aStrings = Split(strParam, " ", -1, 1)


  GetToken = aStrings(intTokenNumber - 1)

End Function


 Now run the query.  You should see the correct data.  Switch back to design view, click on query/append.  You'll be prompted for the table name you want to append to.  This will be the new table.

After that, you'll see a new row in the grid called "append to".  On that row, indicate which fields each column should be placed into.  Then execute the query.

Create another query for the 2nd item the same way but for item and price:

NewItem: Mid([Item],4,3)
NewPrice: GetToken([Price],2)

for the 3rd it will be
NewItem: Mid([Item],7,3)
NewPrice: GetToken([Price],3)

and so on.

Jim.
0
 

Author Comment

by:RKeliher
Comment Utility
Worked like a charm! Thank you so much. Great Job!!
RKeliher

BTW - I am trying to requery a field after update on a field that is in the subform. I know I can't do it with the macros, is there a way to do it in an event procedure.
Thanks
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Sure, it's:

=Forms![myMainFormName]![mySubFormControName].Form![mySubformControlName].Requery

 or if the code is in the subform:

 Me![mySubFormControlName].Requery

  You just need to put the actual names of the form/controls in the [].

Jim.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now