Jonas
You really don't want to do that (post an attachment). Not only is it
frowned on in the 'group netiquette, but ask yourself, would you be inclined
to download and open an attachment from someone you don't know. No
offense...
Here's what I understand, but I may be mistaken...
You have a table that lists all categories, e.g., Type 1, Type 2, ...
You have a table that has Employee(ID), Category, and SomethingToDoWithYear,
e.g.
123 Type 1 2003
123 Type 2 2004
111 Type 3 2004
222 Type 4 2003
You have two queries that return something like:
123 Type 1 2003
222 Type 4 2003
and
123 Type 2 2004
111 Type 3 2004
and wish to have a way to show something like:
2003 2004
123 Type 1 Type 2
222 Type 4 ---
111 --- Type 3
What I proposed is taking your Category table, which I assume has
Type 1
Type 2
Type 3
Type 4
Type 5
...
Type n
and using that as your starting point. Your query would start with the
Category table. You would then add your "query1" (the 2003 rows) to your
new query, and join from the Category table to this query1, using a
"directional" join (all from tblCategory, and any matching query1), on the
Type. Add the Employee ID from query1. At this point your new query shows:
Type 1 123
Type 2 ---
Type 3 ---
Type 4 222
Type 5 ---
...
Type n ---
Next, in the same new query, add and join to the query2, with a directional
join, on the Type. Add the EmployeeID field from query2 (to get the 2004
Employees). Your query shows:
Type 1 123 ---
Type 2 --- 123
Type 3 --- 111
Type 4 222 ---
Type 5 --- ---
...
Type n --- ---
The first column holds the Type. The second column holds the EmployeeIDs
from 2003. The third column holds the EmployeeIDs from 2004.
Is this what you were looking for, or am I still missing something?
Regards
Jeff Boyce
<Access MVP>
Jonas said:
Hi Jeff,
sorry for asking again. I don't understand your solution.
If a have to tables or queries with f.e. EmployeeID,ArticleCategoryID and
sales,
for this year and last year and I want to make sure that I get back all
records of both tables/queries in one result query,
in my option I need to have a table/query that included all possible
comibation of employees and articlecategories ?
I attached a access database file with includes an example.
Absatz means sales in german and Mitarbeiter is Employee.ABSATZVORJAHR is
sales of lastyear.
The ABF_ABSATZ_SUB query is the query that brings together sales of last
year with sales of this year,
by using one query with gives back all employee-articlecategory combination.
If it doesn't cost too much time, would you be so kind again and look into
the database.
I think it a really interesting problem, and I'm sorry for not understanding
your solution.
So if you have a minute, I would be soo happy !
Thanks your your time and engery.
Regards
Jonas
have
a
table listing all possible ArticlesCategories, there's your list of all
types. Then create the "directional" join to find This and Last
records
and
who/how much.
--
Good luck
Jeff Boyce
<Access MVP>
Hi Jeff,
no thats exctactly what I need.
To be honest I had this solution before, but I am not really happy about
it.
Because you have to have a query thats gives you all combitatation of
employees and articlescategories and then another query that filters out
all
rows which have NULLs in the sales of this year and leastyear.
Another even bigger problem is when other fields like quantity unit should
also be shown,
then you have you make your cross product table even bigger which
unfortuantly has the effect in Access2k that Access is not able to handle
those slq querys anymore. Trying to save such a query fails with a crash.
Thanks so much for help !
If you have a solution for getting the query more stable that'll be
wonderful.
Regards
Jonas
Newsbeitrag Jonas
Ah, so you want to see every possible ThisYear and LastYear record,
whether
or not there are values matching between the years?
One way to approach this would be to first create all possible types,
then
connect the "types" query to the LastYear query, on type, with a "Left
Join"
(i.e., all types, and any matching LastYear records), and to the
ThisYear
query, on type, with a "Left Join" (i.e., ... you know!).
This would have the effect of guaranteeing a row for every type, whether
it
had any LastYear or any ThisYear records. Does that meet your
needs,
or
do
you then need to eliminate rows without ANY LastYear OR ThisYear values?
--
Good luck
Jeff Boyce
<Access MVP>
Hi Jeff,
Thanks for your help. I understood your advice.
Unfortunately I think it doesn't the problem of having a record like
Schmidt Type3 600,00
in Query1 and another record like
Gross Type4 2500,00
If I join the data you can see at the bottom of the message I will
always
get only records
eahter of Query1 or Query2 or records with are equal in the joined
fields.
What I need is all data in one query !
Please look again at my example. There you can find
EmployeeNames
(ist
