select top queries

[sarsmelt]

I've been trying to use a select top query to have access choose the next closest date and time from the list. Basically I want to know the difference between the end of shift 1 and the beginning of shift 2.



I'm having a hard time getting access to choose the correct record for the beginning of shift 2 because my IDs correlate to multiple different queries.

What's happening is that it doesn't like when I want to find the difference between shift 1 and shift 4 for example.

Here's a picture of my database

Each Expr1 should be the next ID's Power_On and each Expr2 should be the next ID's Start_Pour but it's pulling from further down.

I'm not sure what's going wrong.

Thanks, Sarah

[orange]

Could you give us some context surrounding your database and issue? Something along the lines of
"a day in the life of our business" - just so we understand the issue better.

I see 1 table only -- that is somewhat rare for database.

Can you tell us in plain English exactly what you wanted this query to do?

Could you take a few records and show us WHAT the "correct output" should be?

Can you post the SQL for the query you have posted?

[sarsmelt]

are melting metal to make into powder as I understand it (I'm not too involved in the actual process just an intern assigned to making this database).

Yes there are quite a few more tables in this database. This is actually just a query but the rest don't pertain to my specific question.

The rest of the entries in this query are correct. IE: ID 8 Expr1 should pull the 11/5 2:25 AM that it has pulled from ID 17 Power_On and Expr2 should pull 11/5 3:46 AM that it's pulled from ID 17.

My issue is that on some of them, like IDs 5, 6, and 7, are pulling from an ID further down, ID 8 in this case when the should be pulling from IDs 6, 7, and 8 respectively.

Does this help?

[ssanfu]

I agree with Orange.... need more info about your query(s) and dB design.

However, looking at what you provided, I would design the table differently. IMO, the design of the table is in classic "spreadsheet" design - short and fat.
I would design it tall and narrow:

ID (PK - Autonumber - actually a poor name)
EventAction_FK (Long)     << - What happened / FK to table EventAction
Shift (Integer)
FurnaceNum (Integer - NO spaces in name)
EventDateTime (Date/Time)   << - when it happened

The EventAction table:

tblEventAction
---------------
EA_ID (PK - autonumber)
EventActionDesc (Text)

Data Desc would be:
1 Power On
2 Start Pour
3 End Pour

Then it is relatively easy to get the date/time for the end of shift 1 and the beginning of shift 2, depending on what you define as the end of a shift and the start of another.



My $0.02 ..........

[orange]

sarsmelt,

I agree with Steve based on the info. You really don't work on part of a database , or one table out of many.
If you are new to database design, here's some info that may help put things perspective.

Good luck with your project.