System to store monthly sales reports

[corai]

Hi All,

I am using Access 2007 and have a question.

I have a large number of business clients (other companies) and would like to develop a system which allows me to produce a monthly sales report for each company.

I was thinking that I would create a table with each company having a separate record. Column 1 would be Company Name and the rest of the columns would be months (i.e. July 2011, August 2011, September 2011 and so on). A form would be used to update sales information in the table as time goes by. I thought of creating the system like this because I want to keep a record of historical data because I may want to produce quarterly or annual reports in the future. Is there a better way of doing this, though?

Any ideas or comments would be much appreciated!

 

[MikeLeBen]

I would point you to the "tables" section of the forum, as I get the idea this thread belongs there.

On topic:
your idea is flawed because you'd find yourself adding a column each month when you'll need to add new data for the sales (you might do it, but it's a pain in the neck and you're certainly better off not doing it).
I would use two related tables designed as follows:

- tblCompanies
kCompanyID (AutoNumber)
:CompanyName (Text)
Notes, or whatever extra fields you might need)

- tblSales
kPeriodID (AutoNumber)
:fkCompanyID (Number)
eriod (Date)
:Sales (Currency)
possibly other fields)