Using Expression Results In A Table
May 26, 2005
I am writing a basic database to track performance for a stationery company.
Basically the database is used to track how long it takes certain depts to complete a task, so each dept ticks a box when they pass the task to the next dept and this populates a field with the date and time that they tick the box using the expression =IIf([Passed to Collate]="-1",Now()," ")
My problem is the box that you place the expression is also the box that you would select which column of the table the result would be sent to and I'm not sure how else you would run querys to establish total time and average time for the task.
I'm sure I'm missing something easy as I can't see why you wouldn't be able to save the result of an expression to the table and use it in querys/reports etc.
Any help would be greatly appreciated.
Col.
View Replies
ADVERTISEMENT
Jul 5, 2006
I'm trying to run a query that has a 3 expressions within the query. I'm using the following:
Exp2-Exp1 this would be the results for Exp3.
The problem I am having is that I only need the results for Exp3 to be returned if they equal "0".
Any help would be appreciated.....Thanks
View 2 Replies
View Related
Jan 2, 2006
When running a query i have created an expression which is simply 2 fields joined together. JOBLOC:[JOB]&[LOCATION]. I want to link this expression with a field in a second table [SCOPE], but for the life of me can't remember how i did it. Either in query design or SQL.
Thanks for your help!
View 1 Replies
View Related
Feb 7, 2007
I have been building my DB and learning everything from scratch. If anybody could give me any advice as to where I may be going astray, it would be much appreciated as I feel like I've hit a brick wall. I run a building firm and need a DB to look after customer information, write estimates, invoices and ultimately do the accounts.
I think my DB needs are very basic to start with and I'm guessing the system only needs to be fairly simple and straightforward. So far my DB has:-
•Customer form with an in-built Estimate subform - this form gives me a brief outline of the customer and how many estimates I have for him.
•If you then double click on an estimate number (in the Estimate subform) this takes you to my Items form (when I create an estimate normally in Word, I have to break jobs down into their components and price each item individually, hence I made an Items table). Each item has an Item Cost.
•I then run the report “Estimate” and have set up a total calculation box on the report ready to print it out for the customer.
My problem is that I would like to know how I can get the Estimate Total to appear in my Estimates Table and Estimates Subform. So far I have achieved making a crosstab query to calculate the Estimate Total (Amount) for each specific Estimate ID, but I cannot get the Total from the Crosstab Query into the Estimate Table, from which I would probably be able to get it into the Estimate Subform. I have a suspicion I need to build an expression in the Estimate Subform - but.....
Once I am over this problem I will want to turn Estimates into Invoices and have that data displayed in my Estimate Subform too, although I think I have got a good idea of how to do this, I would again be open to comments/advice if offered.
Thanks Steve
View 1 Replies
View Related
Mar 30, 2007
I am very inexperinced with Access, please be kind.
My problem is that from the form view the simple division of 2 #'s are placed in the correct text box I selected, but I am unable to get the value to enter into the data table. Please help.
Thank you
View 4 Replies
View Related
Aug 4, 2006
I am new to MS Access, when I am making any calculation using some expression, then I have to save the result to table, it is not saved. Please anyone suggest the solution for my problem..... and mail at rupedhiman@gmail.com.
Rupinder
View 1 Replies
View Related
Mar 13, 2012
I have a text box on my table form with the expression =([LINES]-[MISSED LINES])/[LINES] which gives me the results I want but I also want this total to go to the "On time %" column in my table.
View 3 Replies
View Related
Jul 20, 2006
Hi everyone,
My query is coming along nicely, but as always once one problem is solved you find another :rolleyes: !
My problem is that I have thus far specified criteria for the field OrdDeliveryCountry, but this field is not filled in unless the delivery address is different from the default address for the customer, therefore it is frequently blank and so the query wasn't finding all records, only those where the Delivery Address was specific to the order.
I want to use the IIf function to make an expression to say (in linguistic terms):
If OrdDeliveryCountry is blank, then use the country in the Customers table.
Sounds simple enough, but the criteria currently is:
WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden")
AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission")
AND ((ORDERS.[DEMO/SALEID]) = 2))
So how do I combine the IIf(expr,truepart,falsepart) with "Is Not x Or x Or x"?I.E. I need to get it to exclude records where OrdDeliveryCountry does not equal one in the list, and if that is blank then the Country field in the Customers table does not equal one in the list?
My attempt is this, but I think I'm way off the mark
SELECT ORDERS.SHIPDATE,
PRODUCTS.[STANDARD TARRIFF NUMBER],
[ORDER DETAILS].[QUANTITY] * [ORDER DETAILS].[UNITPRICE] * (1 - [DISCOUNT]) * (1 - [SPECIAL DISCOUNT]) AS LINETOTAL,
[ORDER DETAILS].QUANTITY,
ORDERS.ORDDELIVERYCOUNTRY,
ORDERS.ORDERID,
[ORDER DETAILS].PRODUCTID
FROM CUSTOMERS
RIGHT JOIN (PRODUCTS
RIGHT JOIN (ORDERS
LEFT JOIN [ORDER DETAILS]
ON ORDERS.ORDERID = [ORDER DETAILS].ORDERID)
ON PRODUCTS.PRODUCTID = [ORDER DETAILS].PRODUCTID)
ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID
WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = IIF(ISNULL([ORDERS]![ORDDELIVERYCOUNTRY]),([CUSTOMERS]![COUNTRY] NOT LIKE "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"),
(([ORDERS]![ORDDELIVERYCOUNTRY]) NOT LIKE "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden")))
AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission")
AND ((ORDERS.[DEMO/SALEID]) = 2))
ORDER BY ORDERS.SHIPDATE DESC;
My thoughts:
Maybe I need to re-structure the WHERE clause?
Would it work if the IIf expresssion was in the SELECT part not the WHERE part?
I would really appreciate some help with this: I'm not sufficiently familiar with structuring statements as complex as this and I don't know all the syntax rules etc.
Thanks in advance!!
View 2 Replies
View Related
Aug 31, 2007
Hi all,
I have a Make-Table Query that has five expressions. I have changed the properties of these columns so that, when you view the query, it shows the column names I have chosen. So, instead of Expr1, I get "Haggis", and instead of Expr2, I get "Cold Toast".
BUT, when this query creates a table, the column headings revert to Expr1 and Expr2. Is there any way to make my custom column headings stick in the new table?
I should add that I frequenty run this query and overwrite the table. Thus, even if I go into the table and change the field headings, as soon as it is overwritten these changes revert.
View 2 Replies
View Related
Jan 1, 2006
Hello all, I'm quite new to Access. I've read many Access tutorials and site but I have yet to found the solution for my problem. So here it is (pardon my english):
Field AmountTotal is
=(Nz([AmountSubTotal])+Nz([AmountSH])-Nz([Discounts]))
I manually input AmountSubTotal, AmountSH and Discounts. I expected an automatic calculation for AmountTotal.
In "Form view" I get the result of AmountTotal that I wanted. But the calculation result doesn't get recorded in my field data table AmountTotal. It just shows blank.
How do I record this automated results into my original table?
Thanks in advance!
View 4 Replies
View Related
Aug 7, 2012
i am trying to clean up all unneccessary queries. i am now trying to get a calculation to apear in a text box.
what i want to calculate is [tblReceipts].[ReceiptCash] If Date()= [tblReceipts].[DateOfReceipt]
am i going about this the wrong way of is my pricipal correct but my expression wrong?
View 13 Replies
View Related
Jul 9, 2012
I've got a Table with a string of data delimited by an "*". The first part of the string before the "*" can be variable in length, so if I use a LEFT expression to break apart the 2 parts of the string on each side of the "*", my results are incorrect. In MS Excel, I can use the following formula to solve the problem:
=LEFT(B6,(FIND("*",B6)))
You are just substituting the number of positions with a Find command that looks for the "*", and returns anything left of the "*". Unfortunately, the brilliant programmers at Microsoft, once again, don't have an Excel function in Access. how to recreate that functionality in a table in Access?
View 4 Replies
View Related
Feb 8, 2006
I'm doing a table for Future Business Leaders of America competitive events assignments.
I want the same table to hold Name and Event Name and be able to sort
However, I want the database to be able to sort to tell me each event that a person has, and at the same time, how many people are in each event. Presently, I have to switch between databases.
On one table, the event name is the primary key, and on the other the person's name is the primary key.
For the event name table, categories are "Event Name" , "Participant 1" , "Participant 2", "participant 3", etc.
For the Person's name table, Categories are "Name", "event 1" , "event 2", "event 3", etc.
I want to have ONE database that will sort lists for all participants in each event AND that will sort into a report or form so that I can inform participants of their events.
How can I do this?
I probably was not very clear, so ask lots of questions if you're unsure.
THANKS A LOT!!
View 4 Replies
View Related
Aug 30, 2005
This is a rael daft question, but how do I get the results of a query into a table?
View 5 Replies
View Related
Aug 16, 2005
ok, i'm not the strongest SQL programmer, so i can't figure this out. i'm working on a report with the following source code:
SELECT vio_access_mid.safety_survey_date, vio_access_mid.violation, vio_access_mid.comments, vio_chemical_mid.violation AS chem_vio, vio_chemical_mid.comments AS chem_comm, vio_chemical_mid.safety_survey_date AS chem_date
FROM vio_access_mid INNER JOIN vio_chemical_mid ON vio_access_mid.lab_id = dbo.vio_chemical_mid.lab_id
WHERE EXISTS
(SELECT ehs_labs.lab_id, tbl_list_building.building_desc, tbl_list_department.department_desc, ehs_labs.lab_room, ehs_list_lab_type.lab_type_desc, ehs_princ_inv.princ_inv_fname, ehs_princ_inv.princ_inv_lname
FROM ehs_labs INNER JOIN tbl_list_building ON ehs_labs.building_id = tbl_list_building.building_id INNER JOIN tbl_list_department ON ehs_labs.department_id = tbl_list_department.department_id INNER JOIN ehs_list_lab_type ON ehs_labs.lab_type_id = ehs_list_lab_type.lab_type_id INNER JOIN ehs_princ_inv ON ehs_labs.lab_id = ehs_princ_inv.lab_id
WHERE (ehs_labs.building_id = '0146') AND (ehs_labs.lab_room = '5678'))
AND (vio_access_mid.safety_survey_date = '6/1/2005') AND (vio_chemical_mid.safety_survey_date = '6/1/2005')
when i run the code, i get the correct amount of records (3) from vio_access_mid, but i also get 3 from vio_chemical_mid when in reality there are only 2 records. i will eventually add other vio_*_mid tables that will have a variety of results (some may have 0 while others might have 4 or 5, etc).
i would eventually like to be able to include the info from the WHERE EXISTS clause in my results, but one thing at a time...
anyone have suggestions on how to get the correct info from each vio_*_mid table?
View 6 Replies
View Related
Oct 13, 2005
Hi!
I have query with one column results
How to put automatically results of this query into one column table
Thank's a lot
View 1 Replies
View Related
Jun 23, 2005
I'm sure this has been asked before, so I'm looking for search hints:
I would like to select query results displayed in a form, by check box, and add them to a table. The results displayed in the query are random and this poses a problem, at least for me anyway. :rolleyes: Any ideas?
Thanks in advance.
View 1 Replies
View Related
Apr 13, 2005
I am trying to create custom "icons" using values retrieved from an access db. Right now, I'm pulling records that match the user's name(Fname) or "default", then assigning the returned values variables and plugging those variables in as the image, URL, and title of the "icon". The problem is that this setup will only retrieve the first record from the db that matches "Fname" or "default" and I want to create seperate "icons" for each record that matches "Fname". I can pull all matching records using the maketable(), but then I don't know how to format these as I have here to make additional "icons". My source code is included below:
Code: <% if Fname <> "" thenopencnset rs = Server.CreateObject("ADODB.RecordSet") SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'" Set rs = cn.Execute(sql, , adCmdText)ID = rs.fields(0)URL = rs.fields(2)Icon = rs.fields(3)Title = rs.fields(4)rs.closecloseCNend if%><a href="<%=URL%>" target="new"><img border="0" title="<%=Title%>" src="menupics/<%=Icon%>.gif"></a><br><font color="#FFFFFF" face="Arial" size="2"><%=Title%></font>
Any help anyone could provide on this would be greatly appreciated. Thanks in advance. -Chris Gordon
View 1 Replies
View Related
Mar 28, 2006
Using Access 2000 is it possible to specify a starting location ie (c:) and using VBA search every sub folder from this point for files ending .mdb or .xls?
I want to be able to build a table with an record showing the file name and directory for each file found.
Any help most appreciated.
Regards,
Dalien51
View 2 Replies
View Related
Oct 10, 2005
I am going to attempt to explain the problem:
I design a form and all my work is based on that form.
I have two tables which I join together as following.
The unfitex field is calculating using fields from both tables as shown below.
Dim db As Database
Dim rst As DAO.Recordset
Dim sqlstr As String
v = Combo37.Value
sqlstr = "SELECT postdecgor.N1, postdecgor.Age, postdecgor.UNFITpc, postdecgor.DECENTpc, postdecgor.HHSRSpc, [UNFITpc]*[sample]/100 AS UNFITex, postdecgor.DECENTex, postdecgor.HHSRSex, [Sample size].[Age dwelling], [Sample size].sample FROM postdecgor LEFT JOIN [Sample size] ON postdecgor.Age=[Sample size].[Age dwelling]WHERE ((postdecgor.N1)='" & v & "'); "
Set rst = db.OpenRecordset(sqlstr, dbOpenDynaset)
Set Form.Recordset = rst
I then set up some textbox which shows the results as following.
N1.ControlSource = "N1"
Age.ControlSource = "age"
UNFITpc.ControlSource = "unfitpc"
DECENTpc.ControlSource = "decentpc"
HHSRSpc.ControlSource = "HHSRSpc"
UNFITex.ControlSource = "numunfit"
UNFITex.ControlSource = "unfitex"
DECENTex.ControlSource = "decentex"
HHSRSex.ControlSource = "HHSRSex"
It works, I can see the results on the screen, however I would like to save the results into a table and I am not quite sure how to go about it.
Can you help??
Thanks for your help
View 1 Replies
View Related
Sep 1, 2007
:confused: I have 3 identical fe be database running each with a table called cars that has identical form values just obviously different data. I want to create a table or query to display all the results from these but whenever i try and make a query i cannot seem to work it out. Anyone got any ideas? :confused:
View 2 Replies
View Related
Jun 9, 2006
Alright, i'm almost sure this isn't the best way of doing this,
But i wanna build a scoring system out of three criteria. cost, delivery, and qaulity. I have those tables built along with a contact table where there over all score will be tallied up. Each contact can have more than one entry in the criteria.
So what I did was i built a query to make a new table for each contact to generate the score and the contact id, then using that ID, i update it to the contact table using the UPDATE function...however wheni have 3 criteria, and 400 contacts..this obviously becomes painfully slow.
The question is, is there a way to directly pull the sql query results (summing the total of each contacts score for each criteria), then storing it into the contact table without making a new table in the process?
View 1 Replies
View Related
Nov 20, 2006
Hi,
I have a list-box(Category) and a 'ok' button on the form.
My OK button has the following [Event Procedure]:
Private Sub OK_Click()
Me.Visible = False
DoCmd.RunSQL "Select [Barg Unit],[Medical Option],[Medical Coverage Tier] FROM RetireeCensus Group By [" & Category & "];"
End Sub
First of all, is my syntax correct in the above codes?
Second, what VBScript codes do I need to add in so that when I click the 'OK' button, a report or a table will show the query results from my 'Select' statement?
Thank you.
View 2 Replies
View Related
Jan 10, 2014
I would like to have my macro/vba send an email based on the results of two tables, or if both tables are null, i'd like the email to state that.
View 2 Replies
View Related
Jun 8, 2005
Good day all
Heres what im trying to do:
in one textfield i have =Count(*)
this returns all the records in my db.
now i want to split it up. i want to count all the records for each month.
my field name is datein_now but its just a normal textfield and not a date field.
example in field: 05/02/02 10:24:31 AM.
also got a datein textfield:05/02/02
i had
Count(*) where datein_now between 05/05/01 and 05/05/31
please help
lee
View 1 Replies
View Related
Aug 2, 2006
Hello,
I have a query that prompts the user for input to generate a report.
I would like to in essence copy that same record set and append it to a different table (archive table).
Not quite sure how to go about it.
Thank you
View 5 Replies
View Related
