Need help with MS Access

Simple database... One table....
Let say you have tracking database to track how many accounts  were checked for the particular company.
The easiest one when you have only one account per company
#    Date                      Company               Count
1    06/01/2013            ABC                       1    
2    06/01/2013            DCR                       1    
3    06/02/2013            XYZ                        1


sometimes you have a company TTT with 3 (more then 1) accounts

You have to enter the same info three times
#    Date                      Company               Count
4    06/01/2013            TTT.1                      1    
5   06/01/2013             TTT.2                      1    
6    06/02/2013            TTT.3                      1
The only difference in company name, the number after dot indicates that the number of accounts more then 1 but instead of manualy typing theh same info two more times it should automatically inserts two lines into database

It should asks you "Do you have more then 1 account?"
if yes it should ask you "how many?"
enter 2 (3-1)
and it will insert into the table all two lines with Company.Count



or may be you have another idea how to do that??
rfedorovAsked:
Who is Participating?
 
duttcomCommented:
I would have two tables - one for companies, which has one record only for each company. This would include any address or other details which the company has regardless of how many accounts they have. The number of accounts is not something that really needs to be recorded. It would look something like this -

COMPANY TABLE
#    Date                      Company
1    06/01/2013            ABC
2    06/01/2013            DCR  
3    06/02/2013            XYZ
4    05/02/2013            TTT  

Then I would have a second table which hold the account information. These accounts would be linked by Company (since the company only exists once, but can have many accounts). It might look like this -

ACCOUNTS TABLE
#    Company       Account
1    ABC                     1
2    DCR                     1
3    XYZ                      1
4    TTT                      1
5    TTT                      2
6    TTT                      3

Then if you have a form which allows you to view each Company, you can create a subform which shows all of the accounts which have the same Company. You can use Wizards to help with that. That way you can add or delete accounts without having to rename things. For example, with the structure above, if ABC opened a second account, your accounts table would gain the record "7    ABC   2" and the Company record would not need to be changed. Whereas your current method would require you to go in and make the existing ABC ABC.1 so you could add ABC.2 - not the best way to make use of Access's relational capabilities.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.