Hi to all,
I put together an access database that will be used in the Quality department. One section of the database is I am trying to calculate Crew Hold %. The database contains lots of forums, tables, macros and queries. I am an average user of access. It might be simple but I just cant get to figure it out. I will explain the section of the database that will need to calculate Crew Hold %
First - Crew Hold % = ((Crew)/(Crew+QC))*100. I will need to calculate the expression in the query which depends on Date.
To get the above expression calculated I have a table (tblNCR). The table is populated from a form (frmNCR) the main fileds that will feed into the equation are Date, Cases Disposed, Line # (extracted from Date Code field using the Mid()combined with Len() functions done that) and then there is a field that is a combo box with 2 choices Crew or QC.
This what I did so far, which I dont know if its the right approach but I am open to suggestion and will take any help.
First query (qryForPrideReport).....In the query I have 4 columns Date, Line#(extracted from the Date Code field in the tblNCR) Cases Disposed and Hold (Crew or QC).
Second query (qryCrewHoldCases) that get information from (qryForPrideReport).....In the query output I have 3 columns Line#, date and Crew Hold Cases (calculated using Crew Hold Cases: IIf([Hold]=2,[TotalCases],0))
Third query (qryQCHoldCases) that get information from (qryForPrideReport).....In the query output I have 3 columns Line#, date and QC Hold Cases (calculated using Crew Hold Cases: IIf([Hold]=1,[TotalCases],0))
I used 2 crosstab queries (crosstab2 & crosstab3) that will pull information from qryCrewHoldCases and qryQCHoldCases. Hard to explain I have attached a screen shot of the crosstab outputs.
Now I am stuck, I am trying to make my fourth query that will feed on the crosstab (2 & 3) queries to calculate the Crew Hold % expression. And the query needs a date range input by the user and the output should be by Line#
Hence the final output will be a 2 column query Line# Crew Hold % for the date range entered in the form by the user.
Again I am not sure if thats the best approach I am using or if its even doable.
Lastly the output from the query if it works will be displaced on a report.
I will appreciate any help or input on this
Thank you
Happy Holidays to all
I put together an access database that will be used in the Quality department. One section of the database is I am trying to calculate Crew Hold %. The database contains lots of forums, tables, macros and queries. I am an average user of access. It might be simple but I just cant get to figure it out. I will explain the section of the database that will need to calculate Crew Hold %
First - Crew Hold % = ((Crew)/(Crew+QC))*100. I will need to calculate the expression in the query which depends on Date.
To get the above expression calculated I have a table (tblNCR). The table is populated from a form (frmNCR) the main fileds that will feed into the equation are Date, Cases Disposed, Line # (extracted from Date Code field using the Mid()combined with Len() functions done that) and then there is a field that is a combo box with 2 choices Crew or QC.
This what I did so far, which I dont know if its the right approach but I am open to suggestion and will take any help.
First query (qryForPrideReport).....In the query I have 4 columns Date, Line#(extracted from the Date Code field in the tblNCR) Cases Disposed and Hold (Crew or QC).
Second query (qryCrewHoldCases) that get information from (qryForPrideReport).....In the query output I have 3 columns Line#, date and Crew Hold Cases (calculated using Crew Hold Cases: IIf([Hold]=2,[TotalCases],0))
Third query (qryQCHoldCases) that get information from (qryForPrideReport).....In the query output I have 3 columns Line#, date and QC Hold Cases (calculated using Crew Hold Cases: IIf([Hold]=1,[TotalCases],0))
I used 2 crosstab queries (crosstab2 & crosstab3) that will pull information from qryCrewHoldCases and qryQCHoldCases. Hard to explain I have attached a screen shot of the crosstab outputs.
Now I am stuck, I am trying to make my fourth query that will feed on the crosstab (2 & 3) queries to calculate the Crew Hold % expression. And the query needs a date range input by the user and the output should be by Line#
Hence the final output will be a 2 column query Line# Crew Hold % for the date range entered in the form by the user.
Again I am not sure if thats the best approach I am using or if its even doable.
Lastly the output from the query if it works will be displaced on a report.
I will appreciate any help or input on this
Thank you
Happy Holidays to all
