Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help with MS Access

Posted on 2013-07-01
1
Medium Priority
?
249 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
1 Comment
 
LVL 12

Accepted Solution

by:
duttcom earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

916 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