The best way to store datas in access database

[atzdgreat]

Hi pls. Respect my post. Just want to know what are the pros and cons. what is the best way to store 2000 datas every day using acces database only. Do i have to create new database everyday to save 2000 datas or i will save 2000 datas everyday for one month or save daily for a year and create a new one for the nxt yr.. My fields are date, cust_id, cust_debit, cust_credit. By the way this can be access by multiuser. Thank you

[Uncle Gizmo]

You may want to combine the credit and debit field into one field, and then add a credit or debit as a plus or minus value depending. This will make it far easier to do calculations.

Sent from my Pixel 3a using Tapatalk

[Uncle Gizmo]

You might need an extra field to identify the person that keyed in the data.

Sent from my Pixel 3a using Tapatalk

[atzdgreat]

Hi uncle_gizmo thanks for your reply. Im planning to combine all as an array and save it to one fiekd only but i dont think memo cant handle that. So do you think 1 database for a year can handle the 2000 datas a day for 1 yr?

[orange]

Im planning to combine all as an array and save it to one fiekd only but i dont think memo cant handle that.

This approach is a non-starter. Watch some youtube videos related to database and business processes, analysis and design.
See the link on Database Planning and Design in my signature for more info.

Good luck.

[ArviLaanemets]

Im planning to combine all as an array and save it to one fiekd

You mean a table with single string field, and all entry data as delimited values in it? Probably it doesn't affect much the size of database, but it affects a hell out when running queries and/or reports. Or you are planning enter all data and forget it at all after that?!
a) You can't get any positive effect from indexing such field - any queries will be processing slower;
b) You have to use formulas to read any info from this field - it takes some processing time;
c) You have convert string values read from this field to numbers, dates, and boolean values (dates, debit/credit values in your case) - it takes processing time;
d) Applying filter conditions when reading data causes second set of b)...c) triggered for every condition value applied - more processing time.

As result, query or report may run an order or several slower compared with normalized database structure.

only but i do'nt think memo cant handle that.

From Access 2013 and later versions, Long Text fields replaced Memo fields. Short Text fields can be up to 255 characters.

So do you think 1 database for a year can handle the 2000 datas a day for 1 yr?

It depends on size of database, not on number of rows.

Max size of Access database is 2 GB. Without front end - back end databases used, this includes the size of all interface designed, of all tables, and of all index info.

When you want several users to use database, the back end database where all tables are stored is a must. Every user will have his own copy of front end database, with back end database linked to it. There can be front end databases of different design linked with back end database (e.g. different users will have different access to data in back-end database and are allowed to make different operations). 2 GB limit applies separately to front end and to back end.

In case 2 GB is not enough, you can have SQL Server database as back end.
a) SQL Server Express (freeware) - max size 10 GB;
b) SQL Server - max size 524 PB.