Creating databses for variable records

[Steve Wathen]

I need to create a database where each record is an invoice originally
stored as a csv file. Currently these csv files are loaded in excel to
display them. Each invoice has a variable number of rows and variable
number of columns(see example below). The start of each row has a
document reference which is the same for every line of one invoice. How
can i create a database that can store and manipulate this kind of data.
I have only ever used fixed length data before.

Here is an example of the data i need to put in a databasethis shows an
example of how two invoices are represented, invoice number is always
first column of everyrow so could be used as index.(shown as if in excel
spreadsheet)

12345 HEADER 16/09/03
12345 ADDRESS 13 new street dullsville
12345 BILLLINE 17.5%VAT
51421 HEADER
51421 ADDRESS
51421 METER
51421 BILLINE
51421 BILLINE

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Fletcher Arnold]

<Steve Wathen> wrote in message
news:3f*********************@news.frii.net...

I need to create a database where each record is an invoice originally
stored as a csv file. Currently these csv files are loaded in excel to
display them. Each invoice has a variable number of rows and variable
number of columns(see example below). The start of each row has a
document reference which is the same for every line of one invoice. How
can i create a database that can store and manipulate this kind of data.
I have only ever used fixed length data before.

Here is an example of the data i need to put in a databasethis shows an
example of how two invoices are represented, invoice number is always
first column of everyrow so could be used as index.(shown as if in excel
spreadsheet)

12345 HEADER 16/09/03
12345 ADDRESS 13 new street dullsville
12345 BILLLINE 17.5%VAT
51421 HEADER
51421 ADDRESS
51421 METER
51421 BILLINE
51421 BILLINE

Although I can see a variable number of rows per invoice, I don't see a
variable number of columns. It seems to me, that you have 3 columns
InvNumber, LineType and LineData - it's just that some have a null value for
LineData.

You need to decide on the table structure of the new database first. E.g.
decide between 2 table approach (tblInvoices & tblInvoiceItems) which is
more standard or whether it would be easier to use 1 table: tblInvoiceLines.

I suppose it depends how much of an application you have to write around
this, or do you just need the data in Access tables.

Fletcher