Data

P

PJ

I have an excel spreadsheet that I import into a seperate table in my
database. In my query I'm joining one of the field names with another field
name from the other table. My problem is that if the data isn't spelled the
same in each field name (example Bank Of America in one table vs Bank of
America N.A. in another table) then the rest of the data doesn't show up. Can
this be corrected anyway?
 
A

Allen Browne

PJ, this is a good example of how a spreadsheet differs from a database.

This kind of thing is quite common when importing. You need to fix the data
manually, and then build a relational data structure so that the data is
reliably tied together in Access.

For any field that has repeating values (like bank names), you need to
create another table. This Bank table will have a BankID as primary key, and
one record for each bank. You will then use the BankID instead of the bank
name in whatever table you have (accounts perhaps?)

For this kind of data, you might finish up with:
- Bank table (one record for each bank):
BankID AutoNumber primary key
BankName Text
...

- Client table (one record for each company or individual):
ClientID AutoNumber primary key
MainName Surname or company name
...

- Account table (one record for each account):
AccountID AutoNumber primary key
AccountNum Text
ClientID Number relates to Client.ClientID
BankID Number relates to Bank.BankID

- Transaction table
TransactionID AutoNumber primary key
TransactionDate Date/Time
AccountID Number relates to Account.AccountID
Amount Currency
Direction Number -1 or 1 (debit or credit)

and so on.
 
Top