Import data monthly?

[Japandave]

Hey, I am very new to access and have done as much research as I can over the last few months.

What I am trying to accomplish is import data from a CSV file every month and have it stored as a table and have that table automatically be converted into a printable invoice. I can do the import with no problem. But I am at a bit of a loss as to how I can go about creating the database.

There is only one client that I have to make up an invoice for, but will be more in the future. I need to store the data for each months invoice and then get yearly reports off that data.

I understand the fundamentals of access, but I was hoping someone could just point me in the right direction to get started.

#1, How would I go about connecting each months data?
#2, Should I be using a report to create the invoice layout?


#3, How do I go about automating creating the invoice from the imported data? (I am at an intermediate level with VBA, but access seems to be a whole different ball game)

[June7]

A table for each month is bad design. The data should be imported by appending into one table.

Don't understand question #1 - connect the data where, you are importing it.

#2. yes a report is correct vehicle to produce an invoice.

#3. The report would have a RecordSource that is the table or a query that joins tables.

Access Help as guidelines on all of these techniques.

[Japandave]

A table for each month is bad design. The data should be imported by appending into one table.

Don't understand question #1 - connect the data where, you are importing it.

#2. yes a report is correct vehicle to produce an invoice.

#3. The report would have a RecordSource that is the table or a query that joins tables.

Access Help as guidelines on all of these techniques.

You answered question #1, I was thinking of making a new table for every month. I thinking that I had to connect the tables if I imported it as a new table each month.

How many rows of data can an access hold? Each month has about 4000 rows of data?

Am I correct in assuming I will need another table for the customers info?

[Palomino33]

Hi Japandave, I'll try to help too....but June7 is far more the expert!
#1: Can you get your file in Excel?
#2: Is your file already set up with headings? or do you have to set those up each time you get your file?

If the answer to #2 is yes, then I would set up my access db with those same headings.
When you create a database, first thing it asks is to give it a name. Do that, then you will start out with a blank db.
1. Create Table first....using those same headings as your import file. You will have your ID (primary key), and then the headings for your file.
2. Seems like you only have one customer, but if you have the potential to have more, then yes, set your customers up in a separate table. (Do this one first.) So that when you set your customer field up in your main table....you will do a lookup to your customer table...and that will create your relationship.
2. Then create your query.
3. You can set date criteria/parameters in your query. and then base your report on your query.
4. Download the Northwind template and look around it. See if it gives you any ideas.

[June7]

There is no limit to the number of rows in a table. There is an overall file size limit of 2gb. Run Compact & Repair periodically, especially after design changes because that will cause the file size to grow.

[Japandave]

Thanks guys.