Urgent Access Database Assistance

[ETCallHome]

Hi,



I am trying to create a Table that holds statistical information daily, weekly, monthly, quarterly, half-annually and annually. Bear in mind that Access limits the number of fields in the table to 255. I need to keep this information for every product. I also need to ensure that this information is held on a yearly basis, see below.

Being a Legacy Developer, I know how to do this from a mainframe as the files are sequently, however with OOP the tables are direct access. In mainframe, I multiple array fields and various dimensions to accomplish it, but I am a bit dumbfold how to do this in Access.

Please Help

Year			Product Name
	January '2014		Daily	Weekly	Monthly	Quarterly	Half-Annually	Annually
		1	1	5	20	60	120	240
		2	2	10	40	120	240	480
		3	1	5	20	60	120	240
		4	2	10	40	120	240	480
		5	1	5	20	60	120	240
		6	2	10	40	120	240	480
		7	1	5	20	60	120	240
		.
		.
		.
	February '2014
		1
		2
		3
	etc

[ItsMe]

Tables are tables, are tables.

Store the events as they happen in table(s) and then use formulas to create a query. The query will retrieve the events from the tables as statistics, statistics based on the query/formula. You can format the data retrieved using Reports and or Forms so it is easily interpreted by the user.


It is not advisable to store statistics/calculated data in your table(s).

[ETCallHome]

Hi ItsMe,

I agree with what you are saying and no I am not storing statistic info in the database, but what I am storing is the actual sales per year, per month, per week, per day, etc, so that I can do the necessary calculations to produce the stats. From a program code point of view, I know how to create 1,2,3, etc dimensional arrays and to manipulate them at will. However, information is stored on the database as rows and column, which means one record can have multiple entries in the database, while in let say cobol, we can define one entry that holds all this info, i.e.

01 parent
02 child (1:3)
03 grandchild (n3)

this means only one entry of parent is stored, with the child and grandchild embedded inside the parent entry, while in access I have to create three different tables with links to each table, I think you have the idea. I have already create the tables, but I am trying to find a simpler method than this, like have a variable declared that can have... thank you ItsMe... Come to think of it, you can store the information in a variable with a comma delimiter and when you read it into you program, you can offload it into an array variable using the split method, and then concatenate them before writing it back to the database.

Regards
ETCallHome

[recyan]

Does below ( at a basic level ) not work in your scenario ?

tblProducts
ProductID - PK
ProductName

tblProductSales
ProductSalesID - PK
ProductID - FK
DateOfSale - Date Field if a Product is sold only once in a day else DateTime field if sold more than once in a day.
UnitsSold

Thanks

[ItsMe]

...Come to think of it, you can store the information in a variable with a comma delimiter and when you read it into you program, you can offload it into an array variable using the split method, and then concatenate them before writing it back to the database

If you decide to go with keeping your Quarterly, etc. data in a table just keep in mind that having 100 plus columns in a single table is not typical and could indicate a normalization issue. Use an additional table and join the new table to the original table to avoid "Committing to Spreadsheet".

Storing some historical data may be beneficial if you are having difficulties creating efficient queries for it or code is so complex it becomes difficult to interpret when you later look at again, at a later date. For instance, complex Accounting/Back Office reporting can be supplemented by writing static data to tables dedicated to said reports.

Let us know if you still have questions.