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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Jeffrey CoachmanMIS LiasonCommented:
Again,...Please post a clear graphical example of the exact output you need based on the sample data...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.