Access 2010 database

Posted on 2013-10-30
Medium Priority
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,
Question by:Jose Rivera-Hernandez
  • 3
  • 2
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.


Author Comment

by:Jose Rivera-Hernandez
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!

LVL 74

Accepted Solution

Jeffrey Coachman earned 1380 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:

lID (PK)

cID (Pk)
c_lID (FK)

scID (PK)
sc_cID (FK)

<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.


Author Comment

by:Jose Rivera-Hernandez
ID: 39627052

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



Author Comment

by:Jose Rivera-Hernandez
ID: 40629815
I agree. I abandoned this table, but agree the points distribution.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

621 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