crosstab query?

[pat67]

Hi, i am trying to combine a table to show dates from last week versus
this week. a crosstab does not allow me to show more than one date per
week. a part may be in the table multiple times each week. the table
has part number, release date and extraction date. so it would look
similar to this

PN Rel Date Ext Date
123 4/25 4/16
123 5/25 4/16
123 4/25 4/25
123 5/10 4/25
123 5/25 4/25

what i am looking to do show the data like this

PN last week this week
123 4/25 4/25
123 5/10
123 5/25 5/25


what this does is show if something was new this week. when i do a
crosstab, all i can see to do is min, max, count, sum etc. i cannot
group the value. any ideas?

Thanks

 

[Bob Barrows]

Hi, i am trying to combine a table to show dates from last week versus
this week. a crosstab does not allow me to show more than one date per
week. a part may be in the table multiple times each week. the table
has part number, release date and extraction date. so it would look
similar to this

PN Rel Date Ext Date
123 4/25 4/16
123 5/25 4/16
123 4/25 4/25
123 5/10 4/25
123 5/25 4/25

what i am looking to do show the data like this

PN last week this week
123 4/25 4/25
123 5/10
123 5/25 5/25


what this does is show if something was new this week. when i do a
crosstab, all i can see to do is min, max, count, sum etc. i cannot
group the value. any ideas?

Thanks

Create a query called qThisWeek using this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

Then, a second query calle qLastWeek with this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1

Then do an outer join between the two queries to get your result:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN

 

[Bob Barrows]

Create a query called qThisWeek using this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

Then, a second query calle qLastWeek with this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1

Then do an outer join between the two queries to get your result:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN

oops, left off the second bit of that ON clause, - it should be:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]

 

[pat67]

Create a query called qThisWeek using this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

Then, a second query calle qLastWeek with this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1

Then do an outer join between the two queries to get your result:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN

oops, left off the second bit of that ON clause, - it should be:

 select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
 from qThisWeek as t left join qLastWeek as l
 on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]- Hide quoted text -

- Show quoted text -

ok. i will try that. thanks

 

[pat67]

Create a query called qThisWeek using this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

Then, a second query calle qLastWeek with this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1

Then do an outer join between the two queries to get your result:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN

oops, left off the second bit of that ON clause, - it should be:

 select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
 from qThisWeek as t left join qLastWeek as l
 on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]- Hide quoted text -

- Show quoted text -

Hey great. That works. Thanks

 

[pat67]

Create a query called qThisWeek using this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

Then, a second query calle qLastWeek with this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1

Then do an outer join between the two queries to get your result:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN

oops, left off the second bit of that ON clause, - it should be:

 select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
 from qThisWeek as t left join qLastWeek as l
 on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]- Hide quoted text -

- Show quoted text -

I discovered an issue. if the dates are not the same this week and
last week, it makes the value in the lastweek column null. i need a
value whatever it is. when i see a null value i assume the value in
this week is new and that is not always accurate. any ideas?

 

[pat67]

Bob Barrows wrote:

Create a query called qThisWeek using this sql:
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])
Then, a second query calle qLastWeek with this sql:
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1
Then do an outer join between the two queries to get your result:
select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN

oops, left off the second bit of that ON clause, - it should be:

 select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
 from qThisWeek as t left join qLastWeek as l
 on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]- Hide quoted text -

- Show quoted text -

I discovered an issue. if the dates are not the same this week and
last week, it makes the value in the lastweek column null. i need a
value whatever it is. when i see a null value i assume the value in
this week is new and that is not always accurate. any ideas?- Hide quotedtext -

- Show quoted text -

also if this week and last week have a part with the same date twice
it shows 4 times. say part 123 has 7/3 twice last week and 7/3 twice
this week it shows

PN lastweek thisweek
123 7/3 7/3
123 7/3 7/3
123 7/3 7/3
123 7/3 7/3

i can only have it twice

 

[Bob Barrows]

Bob Barrows wrote:

Create a query called qThisWeek using this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

Then, a second query calle qLastWeek with this sql:

select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1

Then do an outer join between the two queries to get your result:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN

oops, left off the second bit of that ON clause, - it should be:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]- Hide quoted text -

- Show quoted text -

I discovered an issue. if the dates are not the same this week and
last week, it makes the value in the lastweek column null.

That is what your original post asked for ...

i need a

Err ... what value? There's nothing there to show. I don't understand. Show
me an example, source data and desired results

Why not? You've really failed to explain what makes a value "new". I was
assuming it was a release date for a part that exists this week but not
last. Obviously, there's some other criteria for determining this that you
have yet to explain.

also if this week and last week have a part with the same date twice
it shows 4 times. say part 123 has 7/3 twice last week and 7/3 twice
this week it shows

PN lastweek thisweek
123 7/3 7/3
123 7/3 7/3
123 7/3 7/3
123 7/3 7/3

i can only have it twice

I would modify each saved query to return distinct records. That should
solve it.

 

[pat67]

Bob Barrows wrote:
Create a query called qThisWeek using this sql:
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])
Then, a second query calle qLastWeek with this sql:
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1
Then do an outer join between the two queries to get your result:
select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN
oops, left off the second bit of that ON clause, - it should be:
select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]- Hide quoted text -
- Show quoted text -
I discovered an issue. if the dates are not the same this week and
last week, it makes the value in the lastweek column null.

That is what your original post asked for ...

i need a

Err ... what value? There's nothing there to show. I don't understand. Show
me an example, source data and desired results

Why not? You've really failed to explain what makes a value "new". I was
assuming it was a release date for a part that exists this week but not
last. Obviously, there's some other criteria for determining this that you
have yet to explain.

also if this week and last week have a part with the same date twice
it shows 4 times. say part 123 has 7/3 twice last week and 7/3 twice
this week it shows

PN         lastweek  thisweek
123            7/3           7/3
123            7/3           7/3
123            7/3           7/3
123            7/3           7/3

i can only have it twice

I would modify each saved query to return distinct records. That should
solve it.- Hide quoted text -

- Show quoted text -

the way it is working is like this
last week
123 7/3
123 8/3
123 9/3

this week
123 7/3
123 8/1
123 8/25

the end resut shows

PN thisweek last week
123 7/3 7/3
123 8/1
123 8/25

access doesn't put the 8/3 and 9/3 in the query. after thinking, it is
doing what it says it will do. i am ok witht that part however. i can
work with it.

the second part is more important. when you say distinct records what
do you mean? group by? if so that does not work because if there
really are two dates the same, it will group them and show only one.
if it is something else, please explain.

thanks for your help

 

[Bob Barrows]

the way it is working is like this
last week
123 7/3
123 8/3
123 9/3

this week
123 7/3
123 8/1
123 8/25

the end resut shows

PN thisweek last week
123 7/3 7/3
123 8/1
123 8/25

access doesn't put the 8/3 and 9/3 in the query. after thinking, it is
doing what it says it will do. i am ok witht that part however. i can
work with it.

Oh! So you want it to show something like this?
PN thisweek last week
123 7/3 7/3
123 8/1
123 8/25
123 8/3
123 9/3


If Access supported FULL OUTER jOIN, you could simply change LEFT to FULL
OUTER in the original query I gave you. However, since it doesn't, you'll
have to use a UNION:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]
UNION
select t.PN, l.[Rel Date], t.[Rel Date]
from qThisWeek as t right join qLastWeek as l
on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]

the second part is more important. when you say distinct records what
do you mean? group by? if so that does not work because if there
really are two dates the same, it will group them and show only one.
if it is something else, please explain.

No, I meant using the DISTINCT keyword, as in:

select DISTINCT PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

If you use the UNION suggestion from above, you will not have to do this
part because using UNION without specifying ALL causes Jet to discard
duplicate records anyways.

If that doesn't do it for you, I will need to see sample source data and
desired results that illustrate the problem.

 

[pat67]

the way it is working is like this
last week
123    7/3
123    8/3
123    9/3

this week
123   7/3
123   8/1
123   8/25

the end resut shows

PN      thisweek  last week
123         7/3           7/3
123         8/1
123         8/25

access doesn't put the 8/3 and 9/3 in the query. after thinking, it is
doing what it says it will do. i am ok witht that part however. i can
work with it.

Oh! So you want it to show something like this?
PN      thisweek  last week
123         7/3           7/3
123         8/1
123         8/25
123                        8/3
123                        9/3

If Access supported FULL OUTER jOIN, you could simply change LEFT to FULL
OUTER in the original query I gave you. However, since it doesn't, you'll
have to use a UNION:

 select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
 from qThisWeek as t left join qLastWeek as l
 on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]
UNION
 select t.PN, l.[Rel Date], t.[Rel Date]
 from qThisWeek as t right join qLastWeek as l
 on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]

the second part is more important. when you say distinct records what
do you mean? group by? if so that does not work because if there
really are two dates the same, it will group them and show only one.
if it is something else, please explain.

No, I meant using the DISTINCT keyword, as in:

select DISTINCT PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

If you use the UNION suggestion from above, you will not have to do this
part because using UNION without specifying ALL causes Jet to discard
duplicate records anyways.

If that doesn't do it for you, I will need to see sample source data and
desired results that illustrate the problem.- Hide quoted text -

- Show quoted text -

this is from qlaste week

Material Rel Date
4G3450A02612K1R 3/26/2012
4G3450A02612K1R 4/3/2012
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/17/2012
4G3450A02612K1R 4/24/2012
4G3450A02612K1R 5/1/2012
4G3450A02612K1R 5/23/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/11/2012
4G3450A02612K1R 7/23/2012

this is from qthisweek

Material Rel Date
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/17/2012
4G3450A02612K1R 4/24/2012
4G3450A02612K1R 5/1/2012
4G3450A02612K1R 5/23/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/11/2012
4G3450A02612K1R 7/23/2012
4G3450A02612K1R 7/31/2012

this is the combine query. you see the 6/5 date is in there 4 times.
what it does is show each last week date with each this week date.
thuse 2 times 2. I only want to see it 2 times in the end result. not
4


Material lastweek thisweek
4G3450A02612K1R 4/11/2012 4/11/2012
4G3450A02612K1R 4/11/2012 4/11/2012
4G3450A02612K1R 4/17/2012 4/17/2012
4G3450A02612K1R 4/24/2012 4/24/2012
4G3450A02612K1R 5/1/2012 5/1/2012
4G3450A02612K1R 5/23/2012 5/23/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/11/2012 7/11/2012
4G3450A02612K1R 7/23/2012 7/23/2012
4G3450A02612K1R 7/31/2012


as far as the other, i will try the union query

 

[pat67]

pat67 wrote:

Oh! So you want it to show something like this?
PN      thisweek  last week
123         7/3           7/3
123         8/1
123         8/25
123                        8/3
123                        9/3

If Access supported FULL OUTER jOIN, you could simply change LEFT to FULL
OUTER in the original query I gave you. However, since it doesn't, you'll
have to use a UNION:

 select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
 from qThisWeek as t left join qLastWeek as l
 on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]
UNION
 select t.PN, l.[Rel Date], t.[Rel Date]
 from qThisWeek as t right join qLastWeek as l
 on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]

the second part is more important. when you say distinct records what
do you mean? group by? if so that does not work because if there
really are two dates the same, it will group them and show only one.
if it is something else, please explain.

No, I meant using the DISTINCT keyword, as in:

select DISTINCT PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

If you use the UNION suggestion from above, you will not have to do this
part because using UNION without specifying ALL causes Jet to discard
duplicate records anyways.

If that doesn't do it for you, I will need to see sample source data and
desired results that illustrate the problem.- Hide quoted text -

- Show quoted text -

this is from qlaste week

Material                         Rel Date
4G3450A02612K1R 3/26/2012
4G3450A02612K1R 4/3/2012
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/17/2012
4G3450A02612K1R 4/24/2012
4G3450A02612K1R 5/1/2012
4G3450A02612K1R 5/23/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/11/2012
4G3450A02612K1R 7/23/2012

this is from qthisweek

Material                         Rel Date
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/17/2012
4G3450A02612K1R 4/24/2012
4G3450A02612K1R 5/1/2012
4G3450A02612K1R 5/23/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/11/2012
4G3450A02612K1R 7/23/2012
4G3450A02612K1R 7/31/2012

this is the combine query. you see the 6/5 date is in there 4 times.
what it does is show each last week date with each this week date.
thuse 2 times 2. I only want to see it 2 times in the end result. not
4

Material                     lastweek   thisweek
4G3450A02612K1R 4/11/2012       4/11/2012
4G3450A02612K1R 4/11/2012       4/11/2012
4G3450A02612K1R 4/17/2012       4/17/2012
4G3450A02612K1R 4/24/2012       4/24/2012
4G3450A02612K1R 5/1/2012        5/1/2012
4G3450A02612K1R 5/23/2012       5/23/2012
4G3450A02612K1R 6/5/2012        6/5/2012
4G3450A02612K1R 6/5/2012        6/5/2012
4G3450A02612K1R 6/5/2012        6/5/2012
4G3450A02612K1R 6/5/2012        6/5/2012
4G3450A02612K1R 7/3/2012        7/3/2012
4G3450A02612K1R 7/3/2012        7/3/2012
4G3450A02612K1R 7/3/2012        7/3/2012
4G3450A02612K1R 7/3/2012        7/3/2012
4G3450A02612K1R 7/11/2012       7/11/2012
4G3450A02612K1R 7/23/2012       7/23/2012
4G3450A02612K1R         7/31/2012

as far as the other, i will try the union query- Hide quoted text -

- Show quoted text -

the Union query shows similar to the DISTINCT clause

 

[Bob Barrows]

If Access supported FULL OUTER jOIN, you could simply change LEFT to
FULL OUTER in the original query I gave you. However, since it
doesn't, you'll have to use a UNION:

select t.PN, l.[Rel Date] as lastweek, t.[Rel Date] as thisweek
from qThisWeek as t left join qLastWeek as l
on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]
UNION
select t.PN, l.[Rel Date], t.[Rel Date]
from qThisWeek as t right join qLastWeek as l
on t.PN = l.PN and t.[Rel Date] =l.[Rel Date]

the second part is more important. when you say distinct records
what do you mean? group by? if so that does not work because if
there really are two dates the same, it will group them and show
only one. if it is something else, please explain.

No, I meant using the DISTINCT keyword, as in:

select DISTINCT PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])

If you use the UNION suggestion from above, you will not have to do
this part because using UNION without specifying ALL causes Jet to
discard duplicate records anyways.

If that doesn't do it for you, I will need to see sample source data
and desired results that illustrate the problem.- Hide quoted text -

this is from qlaste week

Material Rel Date
4G3450A02612K1R 3/26/2012
4G3450A02612K1R 4/3/2012
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/17/2012
4G3450A02612K1R 4/24/2012
4G3450A02612K1R 5/1/2012
4G3450A02612K1R 5/23/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/11/2012
4G3450A02612K1R 7/23/2012

this is from qthisweek

Material Rel Date
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/11/2012
4G3450A02612K1R 4/17/2012
4G3450A02612K1R 4/24/2012
4G3450A02612K1R 5/1/2012
4G3450A02612K1R 5/23/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 6/5/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/3/2012
4G3450A02612K1R 7/11/2012
4G3450A02612K1R 7/23/2012
4G3450A02612K1R 7/31/2012

this is the combine query. you see the 6/5 date is in there 4 times.
what it does is show each last week date with each this week date.
thuse 2 times 2. I only want to see it 2 times in the end result. not
4


Material lastweek thisweek
4G3450A02612K1R 4/11/2012 4/11/2012
4G3450A02612K1R 4/11/2012 4/11/2012
4G3450A02612K1R 4/17/2012 4/17/2012
4G3450A02612K1R 4/24/2012 4/24/2012
4G3450A02612K1R 5/1/2012 5/1/2012
4G3450A02612K1R 5/23/2012 5/23/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/11/2012 7/11/2012
4G3450A02612K1R 7/23/2012 7/23/2012
4G3450A02612K1R 7/31/2012


as far as the other, i will try the union query

Here are the results I get using

SELECT DISTINCT t.PN, t.RelDate AS Thisweek, l.RelDate AS Lastweek
FROM qLastWeek AS l RIGHT JOIN qThisWeek AS t ON (l.RelDate = t.RelDate) AND
(l.PN = t.PN);


PN Thisweek Lastweek
4G3450A02612K1R 4/11/2012 4/11/2012
4G3450A02612K1R 4/17/2012 4/17/2012
4G3450A02612K1R 4/24/2012 4/24/2012
4G3450A02612K1R 5/1/2012 5/1/2012
4G3450A02612K1R 5/23/2012 5/23/2012
4G3450A02612K1R 6/5/2012 6/5/2012
4G3450A02612K1R 7/3/2012 7/3/2012
4G3450A02612K1R 7/11/2012 7/11/2012
4G3450A02612K1R 7/23/2012 7/23/2012
4G3450A02612K1R 7/31/2012



With the UNION,
SELECT DISTINCT nz(t.PN,l.PN) As Material, t.RelDate AS Thisweek, l.RelDate
AS Lastweek
FROM qLastWeek AS l RIGHT JOIN qThisWeek AS t ON (l.RelDate = t.RelDate) AND
(l.PN = t.PN)
UNION
SELECT DISTINCT nz(t.PN,l.PN), t.RelDate, l.RelDate
FROM qLastWeek AS l LEFT JOIN qThisWeek AS t ON (l.RelDate = t.RelDate) AND
(l.PN = t.PN);


I get:
Material | Thisweek| Lastweek
4G3450A02612K1R | | 3/26/2012
4G3450A02612K1R | | 4/3/2012
4G3450A02612K1R |4/11/2012| 4/11/2012
4G3450A02612K1R| 4/17/2012 |4/17/2012
4G3450A02612K1R |4/24/2012| 4/24/2012
4G3450A02612K1R |5/1/2012 |5/1/2012
4G3450A02612K1R |5/23/2012 |5/23/2012
4G3450A02612K1R |6/5/2012 |6/5/2012
4G3450A02612K1R |7/3/2012 |7/3/2012
4G3450A02612K1R |7/11/2012| 7/11/2012
4G3450A02612K1R |7/23/2012 |7/23/2012
4G3450A02612K1R |7/31/2012|




Eliminating the DISTINCT from the UNION,

SELECT nz(t.PN,l.PN) As Material, t.RelDate AS Thisweek, l.RelDate AS
Lastweek
FROM qLastWeek AS l RIGHT JOIN qThisWeek AS t ON (l.RelDate = t.RelDate) AND
(l.PN = t.PN)
UNION
SELECT nz(t.PN,l.PN), t.RelDate, l.RelDate
FROM qLastWeek AS l LEFT JOIN qThisWeek AS t ON (l.RelDate = t.RelDate) AND
(l.PN = t.PN);

results in the same 12 records, no repetition.

If I said UNION ALL, I would get results similar to yours