I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT FROM HeciFail1 ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
I have a simple query between two tables joined together by common fields. In my first table (Table 1 - tblLocations) I have information about a building i.e. Location Code, address and total sqft. . In my second table (Table 2 - tblAllocatedSpace), this contains details (Location Code, Room ID, SqFt assigned, etc.) of the space allocated in each building. The two tables are joined together when the “Location Code” in both table match.
In my query, I show the location detail from (Table 1 - tblLocations) and related records from (Table 2 - tblAllocatedSpace). My result looks like the following:
Location Code Sqft Address Assigned Sqft 106067 1,000 600 March Rd 10 106067 1,000 600 March Rd 15 106067 1,000 600 March Rd 12 106067 1,000 600 March Rd 20
The location code, Address and Sqft is rebated each time a space is assigned in (Table 2 – tblAllocatedSpace). When a build a report and need to sum the location Sqft, the number is multiplied by the number of related records in (Table 2 – tblAllocatedSpace). In this example by building total space is 4,000 sqft when I only it to show 1,000.
How do I set to only show the location code and sqft once?
I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.
It mostly works in just trying to make it more functional.
Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).
The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.
I would like to update the names list box based upon which cities and locations are selected (both are multi-select).
Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID
The query looks like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO ORDER BY t_asset_personnel.LAST_NAME;
And the list box wizard hides USER_PHY_ID
By setting the width to zero
If I make my own query in the City_After_Update()
Can I do something like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID .fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration ORDER BY t_asset_personnel.LAST_NAME;
I am trying to create a table from a form. The form has several fields but I need to take the value from 4 separate combo boxes ([cr] +[br] +[tr] and add them, then add the value from one more combo box [inc] to be my beginning value in a table.
I then need to add the last value [inc] to the total and that become the next line in the table. I would the like to add this value [inc] an infinite number of times until it reaches a max number.
The scenario would be something like this cr=3 br=2 tr=3 inc=1.5
So the first total would be 9.5. Then every row after that would be plus 1.5 11 12.5 14 15.5 17 and so on.
This would be a temp table that I would run a query on to let an operator know lengths they can choose from in a combo box. I don't know if this is even possible.
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast ) SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast FROM dbo_examInfo
I have some code that creates a table based off another table. This code works perfect but Im trying to alter my ID field and change it to an auto increment. How can I fix my code so that it alters my ID field?
Code: Private Sub Command0_Click() 'OBJECTIVE: to build a table by extracting some fields from a main database Dim rst As Recordset Dim strSQL As String Dim strSQL2 As String Dim intCount As Integer
I have a numeric field (long integer) in a table. I've set the format to fixed. No matter what I set the decimal places to, auto, 0 or 2, when I enter a value of 0.71, it displays as 1.00
In my form, I've also set the field property to fixed, but it displays the value as 1.
I have a problem in setting the criteria of queries.
I have two inspection methods: ABC and XYZ. Every two years, ABC will be carried out, and all other years, XYZ will be performed. However, information to which factory XYZ is performed is required. If ABC is carried out, then the information can be simply "N.A".
But when I do the query, I therefore need to add an extra field which will show "N.A" if ABC is performed, or the factory name if XYZ is performed (factory name can be retrieved from another table).
I have a crosstab query to summarise the counted string values from another query: E.g.;
TRANSFORM Count(Table1.Viable) AS CountOfViable SELECT QryTable01.productName FROM QryTable01 GROUP BY QryTable01.productName PIVOT Table1.Viable;
As there are some null values returned (ie blank cell in the pivot table)
I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;
So if I add a text box in the report with = [viable] + [Not viable] t Then the report row with the values:
I have tblDefaults that has only one record, containing default values. Next I have tblWebpages that has a Memo field that holds my html data. Lastly I have a Form called frmCreate with a field called Webpage. This Form is based on a tblCreate table.
Whenever I click a command button on my Form, I want to populate the Webpage field on my frmCreate Form with a template from tblWebpages. The template to chose will be based upon the WhichTemplate field in tblDefaults.
In brief, tblDefaults tells me which is the current webpage template and so the field should be populated with that html data.
I am creating a table with a field named Payment ID. I want the table to increment the number of this field automatically when the user is trying to enter a new record, i.e. set the field as an autonumber.
I know that this is very basic, my MS Access skills is really limited.
Im not sure if this is the right area to post this but what i wanted to know is how i would set the field "status" to OutStanding when a record is added into the table.
Ok I'll premiss this by saying it has been many years since I have done much with access so I have forgotten alot and alot of things have changed. However I have been using Dataease alot but switched companies so am back to access.In dataease if I had a form called projects and that form had three fields (employee ID, employee first name, employee last initial) that pulled data from another table other than the projects table I would relate them via the employee ID. projects form employee ID = employee form employee IDthen for the two other fields I would have them lookup their data by using the employee id.projects form employee first name = if (projects employee ID = employee employee ID) then lookup employee employee first name. And the same for the last initial.The question is how do I setup for fields for first name and last initial to do the lookup in access.Employee ID TableEmployee ID #Employee First NameEmployee Last InitialProject TableEmployee ID (linked to employee id table)project idproject nameproject discriptionproject start dateproject end dateproject formproject idproject nameproject discriptionproject start dateproject end dateemployee ID (must match employee table employee id)employee first name (read only field that looks its data up from the employee table based on employee id)employee last initial (read only field that looks its data up from the employee table based on employee id)
PeopleType: ID and description (e.g. 1 student, 2 teacher, etc) PeopleDetails: PeopleType.id, name, gender etc
I have a form that contains subforms
On the Main section of the form the data comes from the PeopleDetails. I want the user to fill in this but I want the PeopleType.id to be set to e.g. 2 so that you can only key in teacher details.
I have been trying to set it using a macro SetValue but i cannot get it to work.
I have a form to enter attendance in that pulls students from a StudentEnrollmentTable based on FacultyName which is selected from a combobox that runs the following code:
Private Sub cboInstructorName_Click() Me.Requery End Sub
A query (StudentAttendanceBYFaculty) is run each time a faculty name is selected. On the form their is a field name TempClassesAttended which is bound to a field of the same name in the StudentEnrollmentTable. Teachers will enter attendance data and run an append query to append the current form records to the StudentAttendanceTable. Each time the form is repopulated the most recent TempClassesAttended values are pulled into the form. This is what is expected.
Now I want to load Null values into the TempAttendance field on the form each time the Faculty selects their name and runs the event. I looked at code online and it seems easy enough, but I don't know enough to make it work. This is the code suggested:
UPDATE TableName SET FieldName = Null OR UPDATE MyTable SET MyField = Null
how to include this into the current event so that the event will return the faculty records with Null values in the TempAttendence field.
I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.
In my query I have a name field where I put the criteria: Forms!Formname.Textboxname
By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.
Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.
I need some syntax in setting 2 criterias for a DLookup query.
I've attached a sample db with 2 tables: Main & Timesheet
I need a "combo" query showing (on the same line) all Qty for Transcodes N, 1 & 2 where the Staff number and TSNum is the same.
I'm sure about the logic but the syntax is letting me down.
I can pull in 1 of the criteria E.g.:
OT1: DLookUp("Qty","ExOT1","[Staff] =" & [Staff])
But can for the life of me not script the second criteria in.
So in my result of ExCombo I'm getting Staff 11 showing 4 under OT1 while I know the result does not apply for TSNum 29832 as indicated hence the need for the 2nd criteria.
Since I have to change some of the values to text inside the query it might be best to have a look at the attached db rather than just suggesting the correct syntax .
What is the best way to set delete for user from the form? All code, or part code and use a query, or something different? What is the best criteria to have user use? I just go to datasheet and zap, but I don't want user going into datasheet.
I have a membership database. A number of members go by their middle names or a nickname rather than their first name. I need to keep track of their real full names, but I want the new-record default of the preferred name field to equal the contents of the first name field. How do I set this in the table design? The table already has 1400 records, and it would be quite a job to manually copy each first name to the preferred name field, a job I would rather avoid.
I have only been playing with MS Access for a little while and I do not know all there is to know yet so I figured I come here to ask a question.
I use MS Access in web design and lately I have been experiencing a problem with my registration form which inserts new users into an MS Access database table.
Lately my registration form has become a target for spammers trying to inject spam in the form of html code into the password field of the online registration form.
I figured that I could deter this from happening by only allowing the letters A - Z and numbers 0 - 9 in the field and not allowing characters like < > and ? and ".
I assume I can do this with a validation rule but I'm unsure as to how I would go about setting this up. Limiting the field size to only 50 characters does not seem to work unfortunately.
Any help on how to set up a validation rule would be appreciated.
I have a query that I manually export as a dbf file (i.e. right-click and export). I manually export because I export to a unique directory each time. However, fields in the query that are of type string always get exported with a length of 255. Is there anyway in SQL that I can set the width a query's text field to a specific value (so that when I export it it isn't 255 characters long)?
I know its probably a simple one but I am new to access, so...
I have a simple membership database and in it I keep a record of who I am expecting to attend on a particular date. This is achieved through having a seperate field for each meeting on a simple yes/no type.
I would like to be able to print a report from a form for a particular date showing everyone that would be attending. I have a list box that shows all the fields. What I cant do is set the date I want in the query from the report, can anyone help?
I am "OK" when it comes to figuring out a formula in Excel, but Access is much different for me. I am looking to set up a calculated field in a report.
The result is find the Client-to-Staff ratio but there are two possible conditions:
