Functions in Queries

[dmanonice]

Hello Again all.



I am in need of your expertise to know if what i want to do is even possible.

I have built the query (or what i am able to do) to display all of the information that I need.

What i need to do now is this:

I need to in the column next to "Load" to use the data from "Time Available", look up a value based on the "Type" and if it is less than that value return a "yes" in the field called "Quick Turn"

the values are as follows from the type A 30 B & C 40 D 45.

The second Part is next to this new "Quick Turn" I need a column that calculates "Load Factor" which takes the Load information, looks at the capacity for the type and returns a percentage answer of how full it is.

The values for that are A 78 B 112 C 134 D 174.

Any guidance would be great.

[isladogs]

1. Redesign your database. You only need one table for the data shown e.g. Add Load to the Details table.
2. Change the datatype of the numerical fields from Short text to Number or the following won't work
3. Add a field to the query QuickTurn: IIf(Load<[TimeAvailable], "Yes","No")

As you haven't defined the load capacity, I can't tell you how to calculate that in your query.
Recommend you don't use spaces in field or object names

[dmanonice]

1. Redesign your database. You only need one table for the data shown e.g. Add Load to the Details table.
2. Change the datatype of the numerical fields from Short text to Number or the following won't work
3. Add a field to the query QuickTurn: IIf(Load<[TimeAvailable], "Yes","No")

As you haven't defined the load capacity, I can't tell you how to calculate that in your query.
Recommend you don't use spaces in field or object names

Thank you for the insight, I am teaching myself Access as I go. The database that I have posted here has had many of the fields that are tracked removed for ease of trial and error.

It is set up in two tables because the information comes from different data sources, one table is entered manually, the other is imported from excel from a computer generated report, and the update frequency of both is not the same. So unless there is a way that would take the manual data and merge it with the computer generated data, I will need to keep the tables separate for the time being as I trust the computer data more than I trust the manual data, but I will keep it in mind.

The QuickTurn regrettably is not a simple if statement, because it varies by type. If a unit is using type A they get 30 minutes, the same unit can use a B/C/D type as well which have 40/40/45 minutes respectively. So the QuickTurn would need to look at the available time, determine if it is less than the time for the type used by the unit and return the yes or no answer. In excel I used to use vlookup and some hidden cells to do this can the same idea be used in access?

[Cottonshirt]

dmanonice said: If a unit is using type A they get 30 minutes, the same unit can use a B/C/D type as well which have 40/40/45 minutes respectively. So the QuickTurn would need to look at the available time, determine if it is less than the time for the type used by the unit and return the yes or no answer. In excel I used to use vlookup and some hidden cells to do this can the same idea be used in access?

strictly speaking the answer to your question is yes, the same idea can be used in ACCESS. you have a table with two fields, a type field containing the letters A, B, C, D, and a time field containing the numbers 30, 40, 40, 45, then you can look up the time required for each type in exactly the same way you did in Excel. except that there is no VLookup() in ACCESS so you would use the DLookup() function instead. same thing.

however, this will not mask the fact that you really do need to follow the very good advice that isladogs gave you, and re-design your collection of tables. they are not, at the moment, a database but you could get there with a little thought and care.


good luck with your project,


Cottonshirt

[isladogs]

As previously stated, this should be one table. Data should NEVER be duplicated.
To manage that change, add your Excel file as a linked table
Then use an append and/or update query to transfer that data to your main Access table.

Once you have that sorted out, we can look again at your QuickTurn field

[dmanonice]

Thank you all for your suggestions, I will look into how I might integrate the two tables in the near future.

[NightWalker]

dmanonice

you could have a table where you import to and then do an append/update query to put it into the table structure that isladogs is suggesting. That would be my approach but I am a novice myself. I am sure there are better ways but I am not that knowledgeable yet.

--Walker