By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Need help on a query

P: n/a
I have a table that stores job milestone dates. The 2 milestones I am
interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18.
All jobs have TypeID 1 only some jobs have TypeID 18. I need a query that
will return the c date for TypeID 18 if it exist else the date for TypeID 1,
for all jobs.

the table structure is the following

Job
TypeID
DateEst

I have the following but it only returns the latest date but sometimes
TypeID 18's date if less then TypeID 1's

SELECT DISTINCTROW tblJobDate.Job, Max(tblJobDate.DateEst) AS MaxOfDateEst
FROM tblJobDate
WHERE (((tblJobDate.TypeID)=1 Or (tblJobDate.TypeID)=18))
GROUP BY tblJobDate.Job
ORDER BY tblJobDate.Job;


Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
If the "ship date" and "revised ship date" were two different fields you could set
criteria for "ship date" to be where "revised ship date" Is Null. This would also have the
advantage of giving you both dates for comparison later to see how well you are meeting
your initial ship dates.

If you have the dates in 2 different fields, but are just using a 3rd field to indicate
which of the 2 dates you are using, you could still use the criteria mentioned above.
However, if this is the case, the 3rd field really isn't needed since you could calculate
which of the two IDs it is by using the criteria mentioned. The general rule of thumb is
"don't store anything you can calculate." Now, what might be a good use for a 3rd field is
a counter to see how many times the "revised ship date" was changed.

--
Wayne Morgan
"paii" <pa**@packairinc.com> wrote in message news:vn************@corp.supernews.com...
I have a table that stores job milestone dates. The 2 milestones I am
interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18.
All jobs have TypeID 1 only some jobs have TypeID 18. I need a query that
will return the c date for TypeID 18 if it exist else the date for TypeID 1,
for all jobs.

the table structure is the following

Job
TypeID
DateEst

I have the following but it only returns the latest date but sometimes
TypeID 18's date if less then TypeID 1's

SELECT DISTINCTROW tblJobDate.Job, Max(tblJobDate.DateEst) AS MaxOfDateEst
FROM tblJobDate
WHERE (((tblJobDate.TypeID)=1 Or (tblJobDate.TypeID)=18))
GROUP BY tblJobDate.Job
ORDER BY tblJobDate.Job;

Nov 12 '05 #2

P: n/a
Thank you for your input.

The dates are in different record because they are they are only 2 "Very
important dates" in a list of milestones needed to complete a job. I could
put all the milestones in the same record but then I would have to change
the table structure anytime I wanted to add a new milestone.

Maybe my structure is wrong, Any input on something better would be very
much appreciated.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:2r*****************@newssvr33.news.prodigy.co m...
If the "ship date" and "revised ship date" were two different fields you could set criteria for "ship date" to be where "revised ship date" Is Null. This would also have the advantage of giving you both dates for comparison later to see how well you are meeting your initial ship dates.

If you have the dates in 2 different fields, but are just using a 3rd field to indicate which of the 2 dates you are using, you could still use the criteria mentioned above. However, if this is the case, the 3rd field really isn't needed since you could calculate which of the two IDs it is by using the criteria mentioned. The general rule of thumb is "don't store anything you can calculate." Now, what might be a good use for a 3rd field is a counter to see how many times the "revised ship date" was changed.

--
Wayne Morgan
"paii" <pa**@packairinc.com> wrote in message

news:vn************@corp.supernews.com...
I have a table that stores job milestone dates. The 2 milestones I am
interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18. I need a query that will return the c date for TypeID 18 if it exist else the date for TypeID 1, for all jobs.

the table structure is the following

Job
TypeID
DateEst

I have the following but it only returns the latest date but sometimes
TypeID 18's date if less then TypeID 1's

SELECT DISTINCTROW tblJobDate.Job, Max(tblJobDate.DateEst) AS MaxOfDateEst FROM tblJobDate
WHERE (((tblJobDate.TypeID)=1 Or (tblJobDate.TypeID)=18))
GROUP BY tblJobDate.Job
ORDER BY tblJobDate.Job;


Nov 12 '05 #3

P: n/a
To continue the way you are going, the only thing I can think of is to create a query that
will retrieve all of the records that have a "revised delivery date". Use this query and
the current table in an "unmatched query" using the field that is common to all records of
the same job as the field to not match. You would then take the results of this query and
the first one and make a "union query" to merge them together. Perhaps a SQL guru could
come up with something smoother, something keeps telling me that a subquery might be able
to do this, but I haven't figured out how yet.

Yes, I think putting them all in the same record for the same job would be easier and just
add a field if you come up with a new item for jobs to meet. Do these new items happen
often?

--
Wayne Morgan
"paii" <pa**@packairinc.com> wrote in message news:vn************@corp.supernews.com...
Thank you for your input.

The dates are in different record because they are they are only 2 "Very
important dates" in a list of milestones needed to complete a job. I could
put all the milestones in the same record but then I would have to change
the table structure anytime I wanted to add a new milestone.

Maybe my structure is wrong, Any input on something better would be very
much appreciated.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:2r*****************@newssvr33.news.prodigy.co m...
If the "ship date" and "revised ship date" were two different fields you

could set
criteria for "ship date" to be where "revised ship date" Is Null. This

would also have the
advantage of giving you both dates for comparison later to see how well

you are meeting
your initial ship dates.

If you have the dates in 2 different fields, but are just using a 3rd

field to indicate
which of the 2 dates you are using, you could still use the criteria

mentioned above.
However, if this is the case, the 3rd field really isn't needed since you

could calculate
which of the two IDs it is by using the criteria mentioned. The general

rule of thumb is
"don't store anything you can calculate." Now, what might be a good use

for a 3rd field is
a counter to see how many times the "revised ship date" was changed.

--
Wayne Morgan
"paii" <pa**@packairinc.com> wrote in message

news:vn************@corp.supernews.com...
I have a table that stores job milestone dates. The 2 milestones I am
interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18. I need a query that will return the c date for TypeID 18 if it exist else the date for TypeID 1, for all jobs.

the table structure is the following

Job
TypeID
DateEst

I have the following but it only returns the latest date but sometimes
TypeID 18's date if less then TypeID 1's

SELECT DISTINCTROW tblJobDate.Job, Max(tblJobDate.DateEst) AS MaxOfDateEst FROM tblJobDate
WHERE (((tblJobDate.TypeID)=1 Or (tblJobDate.TypeID)=18))
GROUP BY tblJobDate.Job
ORDER BY tblJobDate.Job;



Nov 12 '05 #4

P: n/a
I did not think of the unlatch query followed by a Union, I am going to look
at that. We need this type of table structure our company is constantly
updating its procedures, I recently doubled the number of possible
milestones any job can have without changing the table structures or any
existing reports or forms.

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:3l******************@newssvr31.news.prodigy.c om...
To continue the way you are going, the only thing I can think of is to create a query that will retrieve all of the records that have a "revised delivery date". Use this query and the current table in an "unmatched query" using the field that is common to all records of the same job as the field to not match. You would then take the results of this query and the first one and make a "union query" to merge them together. Perhaps a SQL guru could come up with something smoother, something keeps telling me that a subquery might be able to do this, but I haven't figured out how yet.

Yes, I think putting them all in the same record for the same job would be easier and just add a field if you come up with a new item for jobs to meet. Do these new items happen often?

--
Wayne Morgan
"paii" <pa**@packairinc.com> wrote in message

news:vn************@corp.supernews.com...
Thank you for your input.

The dates are in different record because they are they are only 2 "Very
important dates" in a list of milestones needed to complete a job. I could put all the milestones in the same record but then I would have to change the table structure anytime I wanted to add a new milestone.

Maybe my structure is wrong, Any input on something better would be very
much appreciated.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:2r*****************@newssvr33.news.prodigy.co m...
If the "ship date" and "revised ship date" were two different fields you
could set
criteria for "ship date" to be where "revised ship date" Is Null. This

would also have the
advantage of giving you both dates for comparison later to see how
well you are meeting
your initial ship dates.

If you have the dates in 2 different fields, but are just using a 3rd

field to indicate
which of the 2 dates you are using, you could still use the criteria

mentioned above.
However, if this is the case, the 3rd field really isn't needed since
you could calculate
which of the two IDs it is by using the criteria mentioned. The
general rule of thumb is
"don't store anything you can calculate." Now, what might be a good
use for a 3rd field is
a counter to see how many times the "revised ship date" was changed.

--
Wayne Morgan
"paii" <pa**@packairinc.com> wrote in message

news:vn************@corp.supernews.com...
> I have a table that stores job milestone dates. The 2 milestones I
am > interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18.
> All jobs have TypeID 1 only some jobs have TypeID 18. I need a query

that
> will return the c date for TypeID 18 if it exist else the date for

TypeID 1,
> for all jobs.
>
> the table structure is the following
>
> Job
> TypeID
> DateEst
>
> I have the following but it only returns the latest date but

sometimes > TypeID 18's date if less then TypeID 1's
>
> SELECT DISTINCTROW tblJobDate.Job, Max(tblJobDate.DateEst) AS

MaxOfDateEst
> FROM tblJobDate
> WHERE (((tblJobDate.TypeID)=1 Or (tblJobDate.TypeID)=18))
> GROUP BY tblJobDate.Job
> ORDER BY tblJobDate.Job;
>
>
>
>



Nov 12 '05 #5

P: n/a
I created a query to select all records with TypeID = 18
Then created a query to select all records with TypeID = 1 that were not in
the 1st query.
Then created a union query to combine the 1st 2 queries.

This appears to work. I don't now how efficient it is to pull from the table
3 time.

"paii" <pa**@packairinc.com> wrote in message
news:vn************@corp.supernews.com...
I did not think of the unlatch query followed by a Union, I am going to look at that. We need this type of table structure our company is constantly
updating its procedures, I recently doubled the number of possible
milestones any job can have without changing the table structures or any
existing reports or forms.

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:3l******************@newssvr31.news.prodigy.c om...
To continue the way you are going, the only thing I can think of is to create a query that
will retrieve all of the records that have a "revised delivery date". Use this query and
the current table in an "unmatched query" using the field that is common to all records of
the same job as the field to not match. You would then take the results

of this query and
the first one and make a "union query" to merge them together. Perhaps a SQL guru could
come up with something smoother, something keeps telling me that a

subquery might be able
to do this, but I haven't figured out how yet.

Yes, I think putting them all in the same record for the same job would

be easier and just
add a field if you come up with a new item for jobs to meet. Do these
new items happen
often?

--
Wayne Morgan
"paii" <pa**@packairinc.com> wrote in message news:vn************@corp.supernews.com... Thank you for your input.

The dates are in different record because they are they are only 2 "Very important dates" in a list of milestones needed to complete a job. I could put all the milestones in the same record but then I would have to change the table structure anytime I wanted to add a new milestone.

Maybe my structure is wrong, Any input on something better would be very much appreciated.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:2r*****************@newssvr33.news.prodigy.co m...
> If the "ship date" and "revised ship date" were two different fields you could set
> criteria for "ship date" to be where "revised ship date" Is Null. This would also have the
> advantage of giving you both dates for comparison later to see how well you are meeting
> your initial ship dates.
>
> If you have the dates in 2 different fields, but are just using a 3rd field to indicate
> which of the 2 dates you are using, you could still use the criteria
mentioned above.
> However, if this is the case, the 3rd field really isn't needed since you
could calculate
> which of the two IDs it is by using the criteria mentioned. The general rule of thumb is
> "don't store anything you can calculate." Now, what might be a good use for a 3rd field is
> a counter to see how many times the "revised ship date" was changed.
>
> --
> Wayne Morgan
>
>
> "paii" <pa**@packairinc.com> wrote in message
news:vn************@corp.supernews.com...
> > I have a table that stores job milestone dates. The 2 milestones I am > > interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18.
> > All jobs have TypeID 1 only some jobs have TypeID 18. I need a
query that
> > will return the c date for TypeID 18 if it exist else the date for
TypeID 1,
> > for all jobs.
> >
> > the table structure is the following
> >
> > Job
> > TypeID
> > DateEst
> >
> > I have the following but it only returns the latest date but

sometimes > > TypeID 18's date if less then TypeID 1's
> >
> > SELECT DISTINCTROW tblJobDate.Job, Max(tblJobDate.DateEst) AS
MaxOfDateEst
> > FROM tblJobDate
> > WHERE (((tblJobDate.TypeID)=1 Or (tblJobDate.TypeID)=18))
> > GROUP BY tblJobDate.Job
> > ORDER BY tblJobDate.Job;
> >
> >
> >
> >
>
>



Nov 12 '05 #6

P: n/a
Union queries are not efficient, but they work, which is sometimes more important. Glad
you got it going.

--
Wayne Morgan
"paii" <pa**@packairinc.com> wrote in message news:vn************@corp.supernews.com...
I created a query to select all records with TypeID = 18
Then created a query to select all records with TypeID = 1 that were not in
the 1st query.
Then created a union query to combine the 1st 2 queries.

This appears to work. I don't now how efficient it is to pull from the table
3 time.

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.