Question/Problem

  • Thread starter Thread starter RayMc
  • Start date Start date
R

RayMc

Hello, I'm new to your forum and personally and glad to be a part of
it.

I have been using Access for a few years now, I'm working on a workers
comp project for my employer and it has had me stumped for a few weeks
and now I'm down to crunchtime!!

I start my query using a [payroll] table and linking it with a

table to get total dollar on gross wages for a 1yr period. I get that
number fine... That information goes into a make table [wages].

The next query I use [wages] and [JBC] table ( the jbc table is used
only to add job "descriptions" to the employees. I link the two (2)
tables together properly and again I get the same dollar amount as the
"wages" table....That info goes into a table called [descriptions]

NOW HERES MY PROBLEM!!!

I take the [descriptions] table and link it with the [classification
table] that was created using Excel and imported into Access.

At this point when I run the query to add in my classification codes
from the classification table. This information goes into a table
called "Final Classification"

I take the Final Classifiation table and put it into a crosstab query
to get my totals for each location/wk state/ and classification code.

When that query is done; the total dollars should balance back to the
the very first query "gross wages" but it doesn't. Its usually lower
the very first query.

I ran all of the queries without using the sum function on anything. Is
this my problem? How should I run this query in order to get the same
total wages all the way through.

ANY HELP OR SUGGESTIONS WOULD BE GREATLY APPRECIATED.
 
Have you tried a totals query on top of the Final
Classifications before you do the CrossTab? If you have the
descrepency at that stage, it could be that not all of the
records have the necessary link to the classification table.
I would try checking it at every level up to the crosstab as
a process of elimination. Probably just doing a Sum on total
dollars may be sufficient for the troubleshooting.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
RayMc said:
Hello, I'm new to your forum and personally and glad to be a part of
it.

I have been using Access for a few years now, I'm working on a workers
comp project for my employer and it has had me stumped for a few weeks
and now I'm down to crunchtime!!

I start my query using a [payroll] table and linking it with a

table to get total dollar on gross wages for a 1yr period. I get that
number fine... That information goes into a make table [wages].

The next query I use [wages] and [JBC] table ( the jbc table is used
only to add job "descriptions" to the employees. I link the two (2)
tables together properly and again I get the same dollar amount as the
"wages" table....That info goes into a table called [descriptions]

NOW HERES MY PROBLEM!!!

I take the [descriptions] table and link it with the [classification
table] that was created using Excel and imported into Access.

At this point when I run the query to add in my classification codes
from the classification table. This information goes into a table
called "Final Classification"

I take the Final Classifiation table and put it into a crosstab query
to get my totals for each location/wk state/ and classification code.

When that query is done; the total dollars should balance back to the
the very first query "gross wages" but it doesn't. Its usually lower
the very first query.

I ran all of the queries without using the sum function on anything. Is
this my problem? How should I run this query in order to get the same
total wages all the way through.

ANY HELP OR SUGGESTIONS WOULD BE GREATLY APPRECIATED.
Click to expand...
 
Gary when you say totals query do u mean take the result of the finals
table and placing it into query with sum on the wages!!!

If thats what you're saying then no, I haven't tried that but I will go
in the office tomorrow and try it.

Keep in mind i have got the same result on the wages, and description,
but when I get to the finals table is where I have the problem...

Thanks for your help
 
PS... the classification table = actually everything in the [description
field] of the [jbc] table, with the exception of the [classification
codes] which are done in Excel and then imported as table into
access....
 
Ray,

What I am saying is to do a total of the wages on each table
at each stage so that you know at which stage things start
to go out of whack and then you know to look closely at the
previous step you performed on it. With the query where you
join the Classification table take a look at your join and
make sure that it isn't limiting the records being returned
from the table holding the wages if the problem shows up on
the Finals table. If the finals is OK, it is probably
something in the CrossTab.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Thanks Gary!!! Just one more question before I go to the office....

When I look at the join; am I looking through the SQL view or am I just
looking at the query itself in design view and looking to see where the
two(2) table join...

Thanks for all of your help!!!
 
Ray,

If you double-click on the join line between the tables in
the query design view you will get the join properties. The
choices are...

Where there are records in both tables

Show all from tblA and only those from B that have a
matching record

Show all from tblB and only those from A that have a
matching in B.

The first is usually the default, but if you are missing
some in B, you may not see some of the records in A. This is
a potential for your problem.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Thanks alot Gary, I was able to bring in another field in my
classification table and made a new join,,, and it worked perfect...

Thank you very much
 
Glad to help!

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Back
Top