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;
6  2303 
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;
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;
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;
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;
>
>
>
>
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;
> >
> >
> >
> >
>
>
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: lawrence |
last post by:
I've been bad about documentation so far but I'm going to try to be
better. I've mostly worked alone so I'm the only one, so far, who's
suffered from my bad habits. But I'd like other programmers...
|
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: pw |
last post by:
Hi,
I am having a mental block trying to figure out how to code this.
Two tables:
"tblQuestions" (fields = quesnum, questype, question)
"tblAnswers" (fields = clientnum, quesnum, questype,...
|
by: K. Crothers |
last post by:
I administer a mechanical engineering database. I need to build a
query which uses the results from a subquery as its input or
criterion.
I am attempting to find all of the component parts of...
|
by: google |
last post by:
I have a database with four table. In one of the tables, I use about
five lookup fields to get populate their dropdown list. I have read
that lookup fields are really bad and may cause problems...
|
by: L. R. Du Broff |
last post by:
I own a small business. Need to track a few hundred pieces of rental
equipment that can be in any of a few dozen locations. I'm an old-time C
language programmer (UNIX environment). If the only...
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
id ------------ name
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
----------------
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
----------------
|
by: pbd22 |
last post by:
Hi.
I need some help with structuring my query strings.
I have a form with a search bar and some links. Each
link is a search type (such as "community"). The HREF
for the link's anchor looks...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
|
by: Naresh1 |
last post by:
What is WebLogic Admin Training?
WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
|
by: Matthew3360 |
last post by:
Hi,
I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
|
by: Ricardo de Mila |
last post by:
Dear people, good afternoon...
I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control.
Than I need to discover what...
|
by: Johno34 |
last post by:
I have this click event on my form. It speaks to a Datasheet Subform
Private Sub Command260_Click()
Dim r As DAO.Recordset
Set r = Form_frmABCD.Form.RecordsetClone
r.MoveFirst
Do
If...
|
by: ezappsrUS |
last post by:
Hi,
I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
|
by: DizelArs |
last post by:
Hi all)
Faced with a problem, element.click() event doesn't work in Safari browser.
Tried various tricks like emulating touch event through a function:
let clickEvent = new Event('click', {...
| |
