About structuring two semi-related queries to display information properly.

[mejia.j88]

Hello all,



I have two queries taht iw ill be using in a report.

Query1 has information on how the parts are built and their electrical yield; and Query 2 is information about the acid used to clean the parts mentioned above.

these two queries don't have a field that ties them uniquely, in other words, no field i can use for a type of join to properly display the information on the report.

About Query 1: in query one, i have a date/time field that, needless to say, records the date/time the parts were dipped in acid. this is done on a daily basis.

About Query 2: in query two we just store information about the acid, e.g. pH levels, concentration, etc. and most importantly, Date used in production. one barrel of acid may last 3 weeks or so.

In a report, i would like to display all of this information, but i am having difficulties tying in the data because there is no unique field unifying the two.

My aim is to have the correct acid information display for the date range of parts cleaned.

an example of a problem i forsee: acid barrels might be added, say, 5/12/12 and 6/2/12, but i want the date range view from 5/2/12-6/5/12. this means that for this date range chosen, there are two barrels, but i dont know how to set up a query that will display this information properly so that i can see what parts were cleaned with what acid.

it is confusing to explain this problem with words.
please let me know if i can clarify anything.

thank you in advanced.
-j

[June7]

So the parts could have been cleaned with either of two acids that were both available on the date of dipping? If you did not save an ID for the acid into the cleaning record, there is no way of knowing which was used. How do we know acid obtained on 5/12 was still available for dipping on 6/1? If you don't record when the barrel is used up, shall a 3-week availability be assumed, even if it was really used up in two? I don't think you can get what you want with insufficient data.

[mejia.j88]

well, when one acid is completly used up, the other is brought in, one start date is the end date of hte previous. i think you are right though, this needs to be stored in the same record, so that each record can have a time frame. given a time frame, i can match up the data with a date range.

in other words, say i view my report from 1/1/12 to 2/1/12, with three acid barrells somewhere within that month. acid barrell 1 could be 1/1/12-1/8/12, barrel 2 from 1/8/12 to say 1/22/12 and barell three from 1/22/12 to 2/?/12.

let me set something like this up and i will get back with teh results. i am not sure how the query will react to a non available end date.

fyi- the acid data will be collected one i finish this db, as of now, it is all dummy data and trying different ways to bring this info together.

thanks june!

[June7]

Okay, barrel availability doesn't overlap. It is possible to get the end date value from the next barrel start date (subtract 1 so end/start dates do not overlap). Requires a tricky subquery. Review http://allenbrowne.com/subquery-01.html

[mejia.j88]

June7,

i am attempting the following subquery based on the allenbrownse tutorial; but am getting a syntax error, and it gives the subquery as the text to look at.

SELECT tbl_etchant_lot_information.id,
tbl_etchant_lot_information.etchant_lot_number,
tbl_etchant_lot_inforamtion.date_time,
(SELECT TOP 1 dupe.date_time
FROM tbl_etchant_lot_information AS dupe
WHERE tbl_etchant_lot_information.etchant_type=dupe.etch ant_type
AND Dupe.date_time < tbl_etchant_lot_information.date_time
ORDER Dupe.date_time DESC, dupe.ID) AS EndDrumDate
FROM tbl_etchant_lot_information;

Is this the correct approach?
i am also thinking of adding another field called EndAcidDate as an alternate option to this subquery issue.

[June7]

It looks okay to me (assuming the space in etchant_type was thrown in there by the website). I would have to test with data to track down the issue. If you want to provide db, follow instructions at bottom of my post.

[mejia.j88]

i have attached the db.

[June7]

Two errors. Misspelling of 'information' and missing 'BY', corrections shown in red:
...
tbl_etchant_lot_information.date_time,
...
ORDER BY Dupe.date_time DESC, dupe.ID) AS EndDrumDate

However, the query results are not correct. You want to retrieve the date of the next drum as the end date for the entered date_time.

Part of problem is the date_time field is not a Date/Time type field, it is text. Must be a Date/Time type. Other part of problem is ORDER BY DESC and < operator. Note the changes. Fix the field type then try:

SELECT tbl_etchant_lot_information.id,
tbl_etchant_lot_information.etchant_lot_number,
tbl_etchant_lot_information.date_time, etchant_type,
(SELECT TOP 1 dupe.date_time
FROM tbl_etchant_lot_information AS dupe
WHERE tbl_etchant_lot_information.etchant_type=dupe.etch ant_type
AND Dupe.date_time > tbl_etchant_lot_information.date_time
ORDER BY Dupe.date_time, dupe.ID) AS EndDrumDate
FROM tbl_etchant_lot_information;

[mejia.j88]

>.< i should have at least caught the information spelling error.

this query works great. i was wondering how i would be able to subtract 1 minute from the EndDrumDate.
This would just make sure i dont have problems when implementing a range of dates on the report.

thanks June7

[June7]

1 second should do it since you are saving the full date/time:

SELECT tbl_etchant_lot_information.id,
tbl_etchant_lot_information.etchant_lot_number,
tbl_etchant_lot_information.date_time, etchant_type,
(SELECT TOP 1 DateAdd("s",-1,dupe.date_time)
FROM tbl_etchant_lot_information AS dupe
WHERE tbl_etchant_lot_information.etchant_type=dupe.etch ant_type
AND Dupe.date_time > tbl_etchant_lot_information.date_time
ORDER BY Dupe.date_time, dupe.ID) AS EndDrumDate
FROM tbl_etchant_lot_information