Using IIF Statement Calculated Column Through Linked Table
Apr 10, 2015
So I have this If statement that goes something like this;
Code:
IIf {ttblEQAnswer_FCLIT.ActionType} = 'Answer Affir Defense' andnot isnull({ttblEQAnswer_FCLIT.METPrep}) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and
({ttblEQAnswer_FCLIT.METPrep})> date(1900,1,1) and not isnull({ttblEQAnswer_FCLIT.AttyInstr})
and {ttblEQAnswer_FCLIT.METPrep} < currentdate - 10
then 'Y' as [MET InstructNeed?],
I want to use the above formula to calculate a column. However, do I need to join the tables referenced above in order to get this formula to work? As it stands I'm getting an error that says "Malformed GUID in query expression".
View Replies
ADVERTISEMENT
May 13, 2015
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies
View Related
May 19, 2014
I have a table with columns as
1- Name
2-Marks in Subject 1
3-Marks in Subject 2
4-Marks in Subject 3
I want to create more columns in same table as
5-Total Obtained Marks
6-Maximum Marks
7-%age of Obtained Marks
How can I do it in one table
View 3 Replies
View Related
Oct 19, 2007
I have a linked table that I want to create some queries for. I would like to add an extra column to the table which is the sum of two existing columns in order to be able to use this in the query. So far, I've come completely unstuck. Any ideas please?
Thanks.
View 1 Replies
View Related
Apr 5, 2013
I have two tables,
Table A:
Year Type Count
1992 Walk 82
1992 Bike 43
1992 Vechicle 298
1992 Ship 3
1992 flight 3478
1993 Walk 47
1993 Bike 13
1993 Vechicle 638
1993 Ship 24
1993 flight 3111
1994 Walk 0
1994 Bike 8
1994 Vechicle 1298
1994 Ship 24
1994 flight 3234
Table B:
ID Type
T1 Walk
T2 Bike
T3 Vechicle
T4 Ship
T5 flight
The two table linked by the [type]
I need a query expression like: SlowWay: IIf([TableB].[ID]= 'T1' AND [TableB].[ID]= 'T2',[TableA].[Count])
FastWay: IIf([TableB].[ID]= 'T3' AND [TableB].[ID]= 'T4' AND [TableB].[ID]= 'T5',[TableA].[Count])
My expression keep gives me the error.
View 4 Replies
View Related
Aug 31, 2007
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number
Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text
Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo
Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode
SF_Parts - SubForm
Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box
UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
F_Estimates - Form
Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription
(all basic Text Boxes)
Employee_ID
Customer_ID
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers
SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
View 10 Replies
View Related
May 22, 2014
I have a statement as follows:
Code:
ohc: IIf(([oh]/[csqty])>1,[oh]/[case_qty] & " (LTSU?)",[oh]/[case_qty])
The intention was that it would add " (LTSU?)" to the calculated number where it was less than 1 (or not a whole number). Stupidm, me didnt account for fractions greater than 1 (ie 1.566). Just wanted to know how I can identify all 'non-whole' numbers in the above IIF statement.
View 3 Replies
View Related
Apr 8, 2015
Access 2013
I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate.
IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12,[CycleTime]*354,IIf(12<=[CycleSpeed]<14,[CycleTime]*472,IIf(14<=[CycleSpeed]<16,[CycleTime]*590,IIf(16<=[CycleSpeed]<=20,[CycleTime]*708,IIf([CycleSpeed]>20,[CycleTime]*944,0))))))
View 6 Replies
View Related
Mar 30, 2007
Table A has a field for Full Name (Bob Jones)
Table B has two separate fields for first and last (Bob and Jones). Table B's contents are actually based on an XML import so I can not just change the table structure.
I want to join the content from these two tables together based on full name.
So first I created a query on table B with a new field created with the expression =[First] & " " & [Last].
I then created a query with table A and query B where I joined the two full-name columns together.
This works perfectly. However, I can't edit the values in this query and I believe from the help files that it's because there's a calculated column in one of the tables.
Is there a way to get around this?
I thought maybe inside my ImportXML VB function once I imported the XML data I could populate an empty column with First + Last but I've read about 50 articles and they're all over my head and I can't figure out how to just concatenate the two columns together into one using VB. It seems if I did this I would avoid having to use a calculated column in a query and this would fix my problems.
Any help would be greatly appreciated. Thanks so much!
View 8 Replies
View Related
Nov 4, 2013
I've now since moved everything into one table but i need formatting of my calculated field (I'm new to all this).
Below are the 3 expressions working independently of one another, I just need them combined in to one if possible. As I am getting an "invalid syntax" error or "too many arguments" when I try to get it working
PHP Code:
0.5*IIf([Type]="Offlist" And [APP_LINE_OFFA]>3,1,0)+0.2*IIf([Type]="Offlist" And [APP_LINE_OFFB]>2,1,0)+
IIf([Type]="Offlist",Abs([EP_DETAILS_OFF]*0.15+[REF_DETAILS_OFF]*0.15),0)
PHP Code:
IIf([Type]="Onlist",Abs([EP_DETAILS_ON]*0.15+[REF_DETAILS_ON]*0.2+[GEN_NOTE_ON]*0.2+[APP_LINE_ON]*0.25+[SPEC_INS_ON]*0.2),0)
PHP Code:
IIf([Type]="Aged Report", Abs([EP_DETAILS_AGED]*0.2+[REF_DETAILS_AGED]*0.2+[GEN_NOTES_AGED]*0.3+[SPEC_INS_AGED]*0.3),0)
View 4 Replies
View Related
Jun 9, 2013
I am trying to group the following Query to find duplicates with no success.
Base Query:
SELECT AccountActivity.AccountID, Mid([details],InStr(1,[details],"-")-1,4) AS FetchedCat, elookup("Neighborhood","Combo","Category='" & [FetchedCat] & "'") AS Neighborhood
FROM AccountActivity
WHERE (((Mid([details],InStr(1,[details],"-")-1,4)) Like "?-??"));
Find Duplicates Query:
SELECT AccountsWithNeighborHood.[AccountID], AccountsWithNeighborHood.[FetchedCat], AccountsWithNeighborHood.[Neighborhood]
FROM AccountsWithNeighborHood
WHERE (((AccountsWithNeighborHood.[AccountID]) In (SELECT [AccountID] FROM [AccountsWithNeighborHood] As Tmp GROUP BY [AccountID] HAVING Count(*)>1 )))
ORDER BY AccountsWithNeighborHood.[AccountID];
View 3 Replies
View Related
Nov 12, 2013
I am trying to add a value to a column in a query based on the value of another column.
I am using an iif statement for it but can't get it to work. I have a column called [Was Worker Born in UK] which has 3 options of "Yes, "No" and "Unknown". The next column is the [COUNTRYOFBIRTH] column.
I basically want [COUNTRYOFBIRTH] to say "866" if [Was Worker Born in UK] is "Yes".
View 14 Replies
View Related
May 23, 2013
I'm having trouble with using a where statement linked to fields when the field is empty. I need a way to say if field is null then 'do nothing'/'select all' else use the text from the box.
I have a form (ServicesRCSSearch) which has 3 combo boxes (Location1, Location2 and Location3). These fields are linked to a query. The button on the form generates the query.
My SQL for the query is currently:
Select Services.Key, Services.Location, Services_1.Location, Services_2.Location
From Services, Services_1, Services_2 (copies of the same table all left joined)
Where
((IIf(forms!ServicesRCSSearch!Location1 Is Null,"",services.Location=forms!ServicesRCSSearch! Location1))<>False)
And ((Services_1.Location)=IIf(forms!ServicesRCSSearch !Location2 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location2))
And ((Services_2.Location)=IIf(forms!ServicesRCSSearch !Location3 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location3))
This works in that it uses the fields to filter the query but when Location1 is empty there are no results as you can see from the code.
View 6 Replies
View Related
Jul 31, 2014
Currently I have a calculated field in my table; however, under certain circumstances I need to change the value to another value which is not related to the calculation. I understand that the calculated fields are read only (why is that?), so I was wondering if there was any way to change values within a calculated column without actually changing the expression itself.
View 1 Replies
View Related
Jul 22, 2013
Code:
strSQL = ("INSERT INTO " & strTable & " Select c.OrderNumber, c.Item, c.RepId, p.ProductNbr, p.Name, [tbl_LBP_Sales Location Num].[Rep Region Code] FROM CalculateTotal c, dbo_PartNew p, [tbl_LBP_Sales Location Num] WHERE ([Structure] like '*" & u & "*') AND ([ProductNbr] = '" & txtPartNumber & "')")
CurrentDb.Execute strSQL
It keeps giving me this error and I don't know why?...
I tried replacing the brackets around Rep Region Code with quotation marks as well as these things `` but kept getting the same error...
View 3 Replies
View Related
Jan 4, 2006
I inadvertently deleted my previous thread. Sargeant reply to my previous thread recommending using the 'Union' statement. I did and below are the results. Below is what I used. Please advise. Thanks again ! ! !
SELECT Table1.Column1, Table1.Column2, ""
FROM Table1
UNION SELECT Table2.Column1, "", Table2.Column3
FROM Table2;
Results
NewTable
Column1 Column2 Column3
A...............................3.1.......
A...............................3.2.......
A...............................3.3.......
A...............................3.4.......
A...............2.1.......................
A...............2.2.......................
A...............2.3.......................
A...............2.4.......................
View 14 Replies
View Related
Nov 3, 2014
I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
View 10 Replies
View Related
Feb 3, 2014
I have to print address labels. 10 x 3.8 cm labels 2 per row 14 per page on an A4 paper. Pretty straightforward one should say. But no. I play around with the margins, column heights and widths, row and column spacing page size wasting time. Is there a hidden and not documented relationship between those elements? For example how is the column height and row spacing linked together? How is this normally done in a professional way?
View 3 Replies
View Related
Mar 5, 2007
Hi all,
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
How do I do this?
Kind Regards,
Matthew
View 1 Replies
View Related
Jun 20, 2013
I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.
Here's the logic:
True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"
False: "GS"
View 2 Replies
View Related
Jul 18, 2007
Please would you be able to advise me how I would copy data from a column in one table to a column another table.
But I need to only use table not a query.
Thank you in advance for your help.
View 10 Replies
View Related
Oct 4, 2005
Hi,
I have an Access 2000 database with some linked tables. When upsizing the database I selected 'Save password and user ID with attached tables'.
Therefore in the Linked Table manager after each table in brackets I have the name of the database to which the tables are linked.
I am now wondering if I want to link the tables to a different database how do I change the database name, which is in brackets after the table name.
If I select a table and select 'prompt for a new location each time' I am getting prompted to select a different DSN. At the moment I don't have a DSN and I don't want to have to set one up.
I imagine that the database name and the user id and password I entered in the upsizing wizard are stored in some configuration box but I don't know where I can access this.
Can anyone point me in the right direction??
Thanks
View 4 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Jan 13, 2014
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
View 1 Replies
View Related
May 28, 2015
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
View 8 Replies
View Related
Aug 29, 2006
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
View 1 Replies
View Related
