Access Query, input data in a row based on data from a diff. row

[CJSilver]

I have a Query, the output looks like this:

Part Number Order# Est. Hours Workcenter Status Buffer

S-CA5143 12500 4.50 1ST END FINISHED
S-CA5147 12570 6.80 1ST END FINISHED
S-CA5143 12500 22.90 ASSEMBLY X
S-CA4890 12215 12.46 1ST END STARTED
S-CA5143 12522 4.50 1ST END
S-CA5147 12570 18.75 ASSEMBLY STARTED X
S-CA4890 12215 27.45 ASSEMBLY
S-CA5143 12522 22.90 ASSEMBLY

This is part of our scheduling report. A job starts in the 1ST END workcenter, after they are finished then the ASSEMBLY workcenter works on it. The buffer column is used to show if a job is ready to be worked on by that workcenter, so in the first row, S-CA5143 is finished in the 1ST END workcenter, so an X would go in the buffer column in the 3rd row. Right now we are manually writing that X in the buffer column, I want to automate that. I tried creating a formula:
iif(([Workcenter]="1ST END" and [status] = "Finished"),"X",null)
but all this does is put an X in the row that has the 1ST END workcenter, which is not what I want. When 1ST END is finished I want the X to go in the row with the ASSEMBLY workcenter.

Any help would be greatly appreciated!

Thanks!

C. Johnson

http://www.SilveradoCable.com

 

[lespaul]

Is BUFFER just a field in this query or do you want to update a table with the value?



Leslie

 

[CJSilver]

lespaul,

Buffer is just a field.


C. Johnson

http://www.SilveradoCable.com

 

[CJSilver]

Any help would be greatly appreciated.

C. Johnson

http://www.SilveradoCable.com

 

[lespaul]

Paste this into the SQL view and see if it does what you are expecting (replace the table name!):

SELECT [Part Number], [Order#], [Est. Hours], [Workcenter], [Status], IIF(([Workcenter]="1ST END" AND[Status]= "Finished"), "X", "") As Buffer FROM TableName

It doesn't really look like there's anything wrong with your statement, perhaps it doesn't like returning null, I replaced null with an empty string.

HTH

leslie

 

[PHV]

You may try this:
SELECT [Part Number], [Order#], [Est. Hours], Workcenter, Status,
(SELECT 'X' AS X FROM yourTable B WHERE B.[Part Number]=A.[Part Number] AND B.[Order#]=A.[Order#]
AND B.Workcenter='1ST END' AND A.Workcenter='ASSEMBLY' AND B.Status='FINISHED') AS Buffer
FROM yourTable A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CJSilver]

PHV,

I'm sorry, I am not following what it is you are telling me to do. I don't understand the A B part of your statement. Would you please use my SQL statement to show me what you mean?

SELECT [New Teams].[CUSTOMER NUMBER], [New Teams].PART_NO, [New Teams].PKG, [New Teams].[ESTIMATED HRS], [New Teams].TEAM, [New Teams].WORKCENTER, dbo_Actual.STAT_FLAG
FROM ([New Teams] LEFT JOIN dbo_Actual ON ([New Teams].PART_NO = dbo_Actual.PART_NO) AND ([New Teams].WORKCENTER = dbo_Actual.WC)) LEFT JOIN [boeing boardcount] ON [New Teams].PART_NO = [boeing boardcount].[S-CA];

There are some columns here that were not in my example. ALso Buffer is not in the SQL statement because I do not have buffer in a table, I was just planning on using it as a formula. If it would be easier for buffer to be in a table I can easily add it.

lespaul,

Your SQL statement does the same thing that my formula does. It adds the X to the row that has the 1ST END workcenter. I need to X to show up in the row with the assembly workcenter.

Thank you both for your help!

C. Johnson

http://www.SilveradoCable.com

 

[PHV]

A and B are just alias to distinguish the different instance of the same table (self join).
Please can you tell me the name of the saved query you posted ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CJSilver]

PHV,

The name of the query? It's called Actual&Team.

Does buffer need to be in a table? what table do I put in SELECT 'X' AS X FROM yourTable

Thank you for your help!

C. Johnson

http://www.SilveradoCable.com

 

[PHV]

You may try this (as a NEW query):
SELECT [CUSTOMER NUMBER], PART_NO, PKG, [ESTIMATED HRS], TEAM, WORKCENTER, STAT_FLAG,
(SELECT 'X' AS X FROM [Actual&Team] B WHERE B.[CUSTOMER NUMBER]=A.[CUSTOMER NUMBER] AND B.PART_NO=A.PART_NO
AND B.WORKCENTER='1ST END' AND A.WORKCENTER='ASSEMBLY' AND B.STAT_FLAG='FINISHED') AS Buffer
FROM [Actual&Team] A;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CJSilver]

PHV,

I thank you for the help, unfortunately I don't know if it works, it is so unbelievably slow, my SQL databases are not that big, but in the 30 minutes since the query opened I still have not been able to scroll to the end of it and it's only a few hundred rows long. Is there another way of doing this? This is a realtime report that our production people need to access on the floor to get their work schedule, I can't have them waiting for a half hour just to get the information they need.

I assume it is the aliases that is making it slow down? I have reports and forms made up of 15 query's that only takes seconds to run. I can't believe just how slow this is.

C. Johnson

http://www.SilveradoCable.com