crosstab query?

  • Thread starter Thread starter pat67
  • Start date Start date
P

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
 
pat67 said:
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
Click to expand...

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 said:
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
Click to expand...

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]
 
Bob said:
Create a query called qThisWeek using this sql:
Click to expand...
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])
Click to expand...
Then, a second query calle qLastWeek with this sql:
Click to expand...
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1
Click to expand...
Then do an outer join between the two queries to get your result:
Click to expand...
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
Click to expand...

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 -
Click to expand...

ok. i will try that. thanks
 
Bob said:
Create a query called qThisWeek using this sql:
Click to expand...
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])
Click to expand...
Then, a second query calle qLastWeek with this sql:
Click to expand...
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1
Click to expand...
Then do an outer join between the two queries to get your result:
Click to expand...
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
Click to expand...

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 -
Click to expand...

Hey great. That works. Thanks
 
Bob said:
Create a query called qThisWeek using this sql:
Click to expand...
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])
Click to expand...
Then, a second query calle qLastWeek with this sql:
Click to expand...
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1
Click to expand...
Then do an outer join between the two queries to get your result:
Click to expand...
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
Click to expand...

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 -
Click to expand...

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?
 
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
Click to expand...
Click to expand...
oops, left off the second bit of that ON clause, - it should be:
Click to expand...
 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 -
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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
 
pat67 said:
Bob Barrows wrote:
Click to expand...
Create a query called qThisWeek using this sql:
Click to expand...
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])
Click to expand...
Then, a second query calle qLastWeek with this sql:
Click to expand...
select PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])+1
Click to expand...
Then do an outer join between the two queries to get your result:
Click to expand...
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
Click to expand...
oops, left off the second bit of that ON clause, - it should be:
Click to expand...
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 -
Click to expand...
- Show quoted text -
Click to expand...

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.
Click to expand...
Click to expand...

That is what your original post asked for ...
i need a
Click to expand...

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
Click to expand...

I would modify each saved query to return distinct records. That should
solve it.
 
pat67 said:
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.
Click to expand...
Click to expand...

That is what your original post asked for ...
i need a
Click to expand...

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
Click to expand...
PN         lastweek  thisweek
123            7/3           7/3
123            7/3           7/3
123            7/3           7/3
123            7/3           7/3
Click to expand...
i can only have it twice
Click to expand...

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

- Show quoted text -
Click to expand...

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
 
pat67 said:
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.
Click to expand...

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.
Click to expand...
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 said:
the way it is working is like this
last week
123    7/3
123    8/3
123    9/3
Click to expand...
this week
123   7/3
123   8/1
123   8/25
Click to expand...
the end resut shows
Click to expand...
PN      thisweek  last week
123         7/3           7/3
123         8/1
123         8/25
Click to expand...
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.
Click to expand...

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.
Click to expand...

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 -
Click to expand...


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 wrote:
Click to expand...
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
Click to expand...
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:
Click to expand...
 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.
Click to expand...
Click to expand...
No, I meant using the DISTINCT keyword, as in:
Click to expand...
select DISTINCT PN,[Rel Date]
FROM table
WHERE Datepart("ww",Date())=Datepart("ww",[Ext Date])
Click to expand...
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.
Click to expand...
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 -
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

the Union query shows similar to the DISTINCT clause
 
pat67 said:
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.
Click to expand...

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 -
Click to expand...

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
Click to expand...


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
 
Back
Top