Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with MS Access

Posted on 2013-07-01
1
Medium Priority
?
248 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 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

715 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