compare data and filter unique

A

AJ

I have a table where we load products made each month. Each month we need to
look at the products ran that month and determine if there are any new ones
that were not run the previous 12 months.

Then we have to look at the month a year ago and determine if any of those
have not been run in the last year and what they are if any.

I can not seem to think how to accomplish this. Any help is greatly
appreciated.

Thank you.
 
M

MGFoster

AJ said:
I have a table where we load products made each month. Each month we need to
look at the products ran that month and determine if there are any new ones
that were not run the previous 12 months.

Then we have to look at the month a year ago and determine if any of those
have not been run in the last year and what they are if any.

I can not seem to think how to accomplish this. Any help is greatly
appreciated.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

New products not in previous 12 months:

SELECT DISTINCT product_name
FROM Products
WHERE introduction_date BETWEEN DateSerial(Year(Date()),
Month(Date()),1) And DateSerial(Year(Date()), Month(Date())+1, 0)
AND product_name NOT IN
(SELECT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -12,
DateSerial(Year(Date()), Month(Date()),1)) And
DateSerial(Year(Date()), Month(Date()),1)-1
)

The function DateAdd("m", -12, DateSerial(Year(Date()),
Month(Date()),1)) returns the beginning of the month, 12 months ago.

The function DateSerial(Year(Date()), Month(Date()),1)-1 returns the
last day of the previous month.

The function DateSerial(Year(Date()), Month(Date()),1) returns the first
day of the current month.

The function DateSerial(Year(Date()), Month(Date())+1, 0) returns the
last day of the current month.



New products 12 months ago, not "run" in previous 11 months:

SELECT DISTINCT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -12,
DateSerial(Year(Date()), Month(Date()),1))
And DateAdd("m", -12, DateSerial(Year(Date()), Month(Date())+1,0))
AND product_name NOT IN
(SELECT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -11,
DateSerial(Year(Date()), Month(Date()),1))
And DateSerial(Year(Date()), Month(Date()),1)-1
)


The function DateAdd("m", -12,DateSerial(Year(Date()), Month(Date()),1))
returns the first day of the month, 12 months ago.

The function DateAdd("m", -12, DateSerial(Year(Date()),
Month(Date())+1,0)) returns the last day of the month, 12 months ago.

The function DateAdd("m", -11,DateSerial(Year(Date()), Month(Date()),1))
returns the first day of the month, 11 months ago (the beginning of the
previous year).

The function DateSerial(Year(Date()), Month(Date()),1)-1 returns the
last day of the previous month (the end of the previous year).

Change the table and column names to suit your set up.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSaxrbIechKqOuFEgEQKO+wCgql7s0mMqtJ3ZEOO+jVIL2/eTOiwAoMpS
AKDtBYSuW2ZiI8vjcb2pLhKR
=MEbX
-----END PGP SIGNATURE-----
 
A

AJ

Thank you.

I get a "syntax" error in the code. Here is what I have in:

SELECT DISTINCTROW DataTbl.[Index], DataTbl.[Market Date], DataTbl.[Prod
Code], DataTbl.[Pkg Code], DataTbl.[Exp Date]
FROM DataTbl WHERE dataTbl.[Market Date] BETWEEN
DateAdd("m",-12,DateSerial(Year(Date()),Month(Date(),1)) AND
DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()+1,0)) AND [Prod Code]
NOT IN (SELECT dataTbl.[Prod Code] FROM DataTbl WHERE [Market Date] BETWEEN
DateAdd("m",-11,DateSerial(Year(Date()),Month(Date()),1)) AND
DateSerial(Year(Date()),Month(Date()),1)-1)


Thank you.
 
A

AJ

I got it, Thank you very much. Very helpful
--
AJ


AJ said:
Thank you.

I get a "syntax" error in the code. Here is what I have in:

SELECT DISTINCTROW DataTbl.[Index], DataTbl.[Market Date], DataTbl.[Prod
Code], DataTbl.[Pkg Code], DataTbl.[Exp Date]
FROM DataTbl WHERE dataTbl.[Market Date] BETWEEN
DateAdd("m",-12,DateSerial(Year(Date()),Month(Date(),1)) AND
DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()+1,0)) AND [Prod Code]
NOT IN (SELECT dataTbl.[Prod Code] FROM DataTbl WHERE [Market Date] BETWEEN
DateAdd("m",-11,DateSerial(Year(Date()),Month(Date()),1)) AND
DateSerial(Year(Date()),Month(Date()),1)-1)


Thank you.
--
AJ


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

New products not in previous 12 months:

SELECT DISTINCT product_name
FROM Products
WHERE introduction_date BETWEEN DateSerial(Year(Date()),
Month(Date()),1) And DateSerial(Year(Date()), Month(Date())+1, 0)
AND product_name NOT IN
(SELECT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -12,
DateSerial(Year(Date()), Month(Date()),1)) And
DateSerial(Year(Date()), Month(Date()),1)-1
)

The function DateAdd("m", -12, DateSerial(Year(Date()),
Month(Date()),1)) returns the beginning of the month, 12 months ago.

The function DateSerial(Year(Date()), Month(Date()),1)-1 returns the
last day of the previous month.

The function DateSerial(Year(Date()), Month(Date()),1) returns the first
day of the current month.

The function DateSerial(Year(Date()), Month(Date())+1, 0) returns the
last day of the current month.



New products 12 months ago, not "run" in previous 11 months:

SELECT DISTINCT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -12,
DateSerial(Year(Date()), Month(Date()),1))
And DateAdd("m", -12, DateSerial(Year(Date()), Month(Date())+1,0))
AND product_name NOT IN
(SELECT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -11,
DateSerial(Year(Date()), Month(Date()),1))
And DateSerial(Year(Date()), Month(Date()),1)-1
)


The function DateAdd("m", -12,DateSerial(Year(Date()), Month(Date()),1))
returns the first day of the month, 12 months ago.

The function DateAdd("m", -12, DateSerial(Year(Date()),
Month(Date())+1,0)) returns the last day of the month, 12 months ago.

The function DateAdd("m", -11,DateSerial(Year(Date()), Month(Date()),1))
returns the first day of the month, 11 months ago (the beginning of the
previous year).

The function DateSerial(Year(Date()), Month(Date()),1)-1 returns the
last day of the previous month (the end of the previous year).

Change the table and column names to suit your set up.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSaxrbIechKqOuFEgEQKO+wCgql7s0mMqtJ3ZEOO+jVIL2/eTOiwAoMpS
AKDtBYSuW2ZiI8vjcb2pLhKR
=MEbX
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop Macro to edit Date 0
Section and Page numbering 0
Calculating what staff worked on which product 3
Filtering data from a non-continuous list 3
dynamically choose columns in query 1
Total by certain time period 1
Querying last months data 4
Project Online Timesheets Actuals Mismatches - data not publishing to projects 0

Top