Compare field in two different tables

[Guest]

I have two different tables showing projected sales. The first table is week
1 and the second table is week 2. The field "Project Month" represents the
month in which the order is planned to complete. As with any sales,
sometimes the project can move out to a different month. What I want to show
is only those sales where the Project Month have changed from the previous
week. Each project has it's own unique project number, so I can key off of
that.

Any ideas?

 

[John Vinson]

I have two different tables showing projected sales. The first table is week
1 and the second table is week 2.

Well... that's a BAD idea right there, I fear. Storing data in
tablenames is *never* good design. Might you not do better to store
projected sales all in one table with a WeekNumber field?

The field "Project Month" represents the
month in which the order is planned to complete. As with any sales,
sometimes the project can move out to a different month.

I'm sorry, I simply don't understand this. You have your own business
rules and jargon which aren't obvious: what's a "project"? what does
it mean for a project to "move out"?

What I want to show
is only those sales where the Project Month have changed from the previous
week. Each project has it's own unique project number, so I can key off of
that.

A Join of your two tables on ProjectNumber, or a self join of the
properly normalized table I suggest, should do this just fine.

John W. Vinson[MVP]

 

[Guest]

The problem is I can't just have one table. The raw data comes from another
system. "Moving out" just means the sale (or project as we call it here) is
having some issues and will not be completed in the month originally
projected.

Thanks anyway.

 

[John Vinson]

The problem is I can't just have one table. The raw data comes from another
system. "Moving out" just means the sale (or project as we call it here) is
having some issues and will not be completed in the month originally
projected.

Well... you can indeed have one table.

If you're assuming that the ONLY thing you can do with this external
file is to create a new table, be aware that you do have other
options; one would be to link to it (rather than importing it) and
Append it to a master table.

I'm not knowledgable about your business or your data structure,
obviously, so I may be off base - but my professional opinion is that
unless you have a *clear and demonstrated* need to denormalize, you
should not do so. Everything you've described can be accomplished
using a single table.

John W. Vinson[MVP]

 

[Guest]

Frank,

My simple example works I believe as you desire. Try:

select w1.[Project ID], w1.[Project Month], w2.[Project Month]
from Week1 as w1 inner join Week2 as w2
on w1.[Project ID] = w2.[Project ID]
where w1.[Project Month] <> w2.[Project Month]
order by w1.[Project ID];

Good Luck!

 

[Frank Timmons Jr]

I don't understand...how could I create a single table using week 1 and week
2 when I receive the raw data, all the fields are exactly the same, I just
need to show what the changes are from the prior week, any new items and any
items that dropped off. (The last two I'm fine with, it's just showing the
items that changed I'm having trouble with).

I tried creating a select query and in the Project Month field of the week 2
table, I used Not Like [tbl_week1]![Project Month]. I also joined the
Project Number between the two tables.

That seems to work ok, but I'm not confident it's showing me everything