CODEWITHSUNDEEP
sqlsql-servervb.netms-accessdata-structures
Jacob D
Jacob D

Reputation: 239

Creating a database structure where one column has multiple values?

I have a SQL database that uses MS Access as a front-end and is coded with VB. Data is imported daily from an Excel spreadsheet in the form:

Journal Date, IO Account, Amount, Posted Date, Line Description, User ID, Date Checked, Voucher

The Voucher data is formatted in the Excel spreadsheet like: 1111111;2222222;3333333

The Voucher can have as many voucher numbers as needed (anywhere from 1 - 13 different num bers usually) and each number is separated by a semi-colon. Also, each number can differ in physical length. I then need to link these voucher numbers to multiple different tables. The data structure that was in place when I took over this project stored the voucher in the semi-colon separated form and parsed the string to link to other tables. I'm getting ready to revamp this database, and I don't want to store the data like this format because I know it violates 1NF.

I know I should probably use foreign keys, but I'm not sure how with the varying Voucher data. I have the ability to parse the data any way possible with VB in Access when it comes from Excel, I just don't know how to store it to relate my tables.

The goal of the project is to match everthing that relates to each distinct voucher and see if the amounts match up. Right now the process takes anywhere from 5 - 10 minutes to split the vouchers and compare the data across tables.

Let me know if you need more information. Any advice would be appreciated. Thanks!

EDIT- Let me be more clear:

Here is an example voucher: 2988670;2990020;2989130;2991597;ONL112382

There are multiple entries in the table (call it T1) with this voucher with differing amounts. Each of the numeric values (excluding ONL number) correspond to another table (call it T2). If each of these 4 numbers are in T2, they are said to be "Matching". If they are matching and the sum of the amounts in T2 equal the sum of the amounts in T1, they are "Matching with no difference".

Sometimes the voucher looks like this: ED414596

In this case, I have to compare this value to a completely different table (call it T3). It includes the same matching process as above.

*Let's just say the voucher comes across in multiple ways and it needs to be compared to multiple tables.

Upvotes: 0

Views: 3868

Answers (1)

ngneema
ngneema

Reputation: 444

Your voucher table would have a voucherID and a voucherNumber, this would allow voucherID to be repeated as many times as necessary. voucherID would be a foreign key from your first table, and the two columns together would be the PK for this table.

Upvotes: 2

Related Questions