473,320 Members | 1,982 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need help on a query

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
6 2399
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
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...
9
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...
3
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,...
7
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...
3
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...
10
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...
1
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
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: ----------------
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: ----------------
3
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.