Solved

Need help with MS Access

Posted on 2013-07-01
1
242 Views
Last Modified: 2013-07-23
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??
0
Comment
Question by:rfedorov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 12

Accepted Solution

by:
duttcom earned 500 total points
ID: 39291890
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question