Solved

Need help with MS Access

Posted on 2013-07-01
1
245 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Suggested Courses

623 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