MS Access data comparison

Using MSAccess 2007

Have two tables...  
Table 1: information entered and only changes annually with effective dates
Table 2: data entered checks Table 1 (compares data in Table 2 with data in Table 1)
and if Table 2 matches or fails matching data in Table 1...  display that information in Table 2 with a code, (A), (B, (C)

 If so, can you explain how?
softsupportAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Ok, then this is a bit more than I can dedicate myself to as a volunteer here.

here is what I was thinking:
examine it carefully
Database168.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
"Matching" is difficult to nail down if you are not specific.

You could have one value in one table, but it could exist twice in another table, ...so here we need to clarify a match, and a one-to-one match
(And what about the unmatched records in either table...?)

In other words, post a sample of both tables, then post the *exact* results you are looking for.

JeffCoachman
0
 
softsupportAuthor Commented:
CTable 1: Stagnant information changing annually, shows size and amount with frequency (Monthly, Weekly, Annually, etc)

CTable 2:  Once data entered, want to see if data in CTable2 falls within range of CTable1 depending upon Frequency and size

If frequency is the same...  display will be based on that frequency
If frequency is mixed.... display needs to be based on annual (which would require conversion; ie: weekly converts to annually [amt]*52, monthy converts to annually [amt]*12, etc)

But for now, just trying to compare data entered in CTable2 to CTable1 and displaying results in Table 2.   Hope this description is clearer.
CTable2.jpg
CTable1.jpg
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Jeffrey CoachmanMIS LiasonCommented:
Again,...Please post a clear graphical example of the exact output you need based on the sample data...
0
 
softsupportAuthor Commented:
A bit complicated, but need some insight on how to tackle this.  Tried to give graphical explanation attached.  Hope you can give me assistance.
App.docx
0
 
Jeffrey CoachmanMIS LiasonCommented:
In looking at what you posted, your question title of "comparison" is not really what is needed here... As far as I can tell, you really need to "determine" (based on calculations) eligibility.

In looking at your diagram there, either of two things is happening:
1. This is a bit more than a single "question".
2. The design is not optimal for what you are trying to do.

So let me try to explain the way I see this in very simple terms.
You have a family
This family has a size
This family has various sources of income, that are paid on different timing schedules
The yearly total of these incomes, coupled with the family size needs to be les than or equal to a predetermined amount in order to be eligible

Is this correct?
0
 
softsupportAuthor Commented:
That is correct.... One change
If the pay schedules are the same(ie Monthly) can be determined Monthly
If the pay scheules are mixed (ie:Monthly, weekly) must be determined Annually.
0
 
Jeffrey CoachmanMIS LiasonCommented:
I don't see a need for the pay schedules to be in two places
If the frequency is defined in a table, you can simply calculate the yearly salary.

Isn't the yearly salary ultimately what you are after? (and the number of family member)
0
 
softsupportAuthor Commented:
No....  If monthly is given and no other frequency...  Monthly must be used.
0
 
Jeffrey CoachmanMIS LiasonCommented:
<No....  If monthly is given and no other frequency...  Monthly must be used. >
I am confused by this,...can you post a graphical example of this?

But again....keeping this simple...
If you calculate the total yearly income for all of that family's income streams (by whatever method is applicable) then you get one yearly salary, correct?

Then you look at the number of family members, then see if their salary makes them eligible or not...
Is this correct?
0
 
softsupportAuthor Commented:
Sorry about confusion.... using table1 info:

1 Family member Monthly is $1,245, (according to table).  When you calculate yearly ($1,245*12 = $14,940) .... family is over Annual limit and will not qualify (according to table).  That is why only mixed frequency are done annually (yearly)  

Hope that explains better.
0
 
Jeffrey CoachmanMIS LiasonCommented:
First I will say thanks for explaining all of this...
I am just tying to understand, so I can provide a more targeted solution.

So in the example above, this family is ineligible.
Are you saying that in your system,  you want this family to now become be eligible?
Yes or No?
If yes, then how would you "magically" reduce the stated income so that they became eligible?


JeffCoachman
0
 
softsupportAuthor Commented:
I know this can be confusing, that is why I need help....  but these are the rules

This family is eligible if they are qualified as monthly, if I qualify them annually they are no longer eligible.  That is why I can only annualize to a yearly income if the income frequency is different.  If it is the income entered is the same...must be qualified that frequency.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help, and I really appreciate the time you have spent explaining all of this.

It's just that as validation and checks like this become more complex, it is hard for us to give you an "exact" solution.

But to be fair, you can re-post this question at a later time and there may be an expert willing to take this on.

;-)

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

All Courses

From novice to tech pro — start learning today.