Solved

Access 2010 database

Posted on 2013-10-30
7
81 Views
Last Modified: 2015-03-03
I would like to create a database to store the following:

Table A
Special Events Total (calculated field to add the other fields)
Special Event A (number)
Special Event B (number)

Table B
Special Home Events
Special Home Events Total (calculated field to add the other fields)
Special Home Event A (number)
Special Home Event B (number)

Table C
Special Event Date
Special Event Total
Special Home Event Total


so it looks simple and I almost got it, but somehow I can't get it to work.

So what I would like is Table A and B to share the Date field from Table C, so this field is unique and how I would like to search for and all tables will share this key field content.

Table C will have a copy of the totals for Table A and B, I get an error that Calculated fields can be in a system relationship.

So my vision is to have a Form where there is:

- Date field (this would be the Date field from Table C)
- Special Event A (number)
- Special Event B (number)
- Special Home Event A (number)
- Special Home Event B (number)
- Total for Special Event (from Table C)
- Total for Home Special Event (from Table C)

I am not sure if there will be subforms and queries need to be created for Special Events and Special Home Events table. Ideally, I open the form, enter the date and data, and the fields (date and totals) replicate to where is necessary.

all my attempts have failed, I have to lookup the date in a drop box and I don't want that, I want the date to show on Table A and B when I enter the date on Table C, and vice versa, when the total is calculated on Table A and B, this number should be displayed on Table C.

Let me know how I can accomplish this, it's been a while since I've use Access and I used to be pretty good at it, but it seems that I need a refresh course!

Thank you,
0
Comment
Question by:hernandez5999
  • 3
  • 2
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39614096
Is your question how to make this work with the design you are specifying.?

Because it is hard to tell, just from that, if the tables are designed properly.

It may be much better if you stated what this database was doing...
Then asked for help in designing/relating the tables...

<all my attempts have failed>
...This is a tip-off that the tables are not properly designed/normalized.

JeffCoachman
0
 
LVL 3

Author Comment

by:hernandez5999
ID: 39615093
The tables are very simple. They are not complex, what I am having problem is passing the data from one table field to another table field and keep them sync.

The database is storing log events. I review over 15 security logs daily (this is why the Date field is important). Each log can have multiple categories and within that sub-categories. Additionally each of these categories have a number of logs that I need to totaled; hence the importance of the Calculated field as well.

Although there are 15 logs, these logs fall under 5 to 6 categories (Network events, Computer events, User Events, Audit Events. File Events, and Malicious Events)

Here is an example of a security log:


Primary Category:  Security Logs = 1200
Category 1= Attack Behavior= 300 events
Sub-Category 1= 150
Sub-Category 2=150
Category 2= Suspicious Behavior= 400 events
Sub-Category 1=100
Sub-Category 2=200
Sub-Category 3 =100
Category 3 = Network Suspicious= 500 events
Sub-Category 1=100
Sub-Category 2=50
Sub-Category 3 =10
Sub-Category 4=40
Sub-Category 5=200
Sub-Category 6 =100

so now multiply these by 15.

The ideal database will be keeping a record of this data and I should be able to search by date or primary category.

I would like an initial form that displays a Date field and then this date is replicated to all the necessary tables. There would be 6 buttons for each Primary Category, when I click on them, a new Form would open where it would have these Sub-categories fields and I should be able to enter the number of events. There should also be a Notes field for any notes that need to be done. Once I entered the number of the events, the Calculated field should totaled the number of events and include it in the Primary Category Form. Furthermore, this same totaled should be displayed on the initial Form where the 6 buttons are displayed. The total should be under the buttons.

So if I want to search last Monday's event, the initial form should be displayed with the Totals for each category and if I want to know further, then click on the button where it would display the Categories and sub-categories numbers and any notes.

I should also be able to add a new Category and Sub-Category also.

I know it sounds confusing, but let me know if I need to create a Visio or image file so you can visualize it better.

Thank you!

J
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 345 total points
ID: 39615917
Then as far as I can tell, your tables are not designed properly to give you what you want.
Are you quite sure that your design is correct?

Just because it seems "simple" does not mean that it will always be...
I see something similar to this:

tblLogs
lID (PK)
lName
lDate
...


tblCategories
cID (Pk)
c_lID (FK)
cName
...


tblSubCategory
scID (PK)
sc_cID (FK)
scName
...

<Each log can have multiple categories and within that sub-categories. Additionally each of these categories have a number of logs that I need to totaled; >
Then this would seem to indicate a Many-to-Many relationship, so an additional table would be needed.
tblLogCategory
lcIC
lc_lID
lc_cID
lcQuant
...

JeffCoachman
0
 
LVL 3

Author Comment

by:hernandez5999
ID: 39627052
Jeff,

Sorry for the late response. I'll try to create the db this week and let you know. Your design makes more sense.

Thanks,

Jose
0
 
LVL 3

Author Comment

by:hernandez5999
ID: 40629815
I agree. I abandoned this table, but agree the points distribution.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article will show you how to use shortcut menus in the Access run-time environment.
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…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now