Queries Based On Same Table
Jun 29, 2005
Hi everyone!
I've got a table that I, with help of a query and many criterias on different fields, can filter so that I get the records I desire to have. I then done a chart out of these records. So far everything works..
but now to the problem...
The table is among other things based on years and quaters. I want to be able to compare different quaters. So what I've done is to make a new query that has the same criteria as the first query except the criteria for years and qutarers. That criteria is taken from other comboboxes. (All the criteria are taken from a form with different combos).
So finaly what I've tried to do is to do a third query based on the two first queries so that I there would be able to take the fields I want and calculate the difference between them. That doesn't work!?...
It says: "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields drom a table and from a query based on that table. If so, try choosing fields from only the table or only the query"
What can I do!??!
Any help would be appreciated
Sorry for my poor english, hope you understand...
Thanks in advance
-Jake
View Replies
ADVERTISEMENT
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
Sep 20, 2014
I'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.
View 3 Replies
View Related
Mar 9, 2015
I would like to achieve the following task described below:
Just for an example i have two tables = Table1 and Table2
Table1 contains following fields: ID , CusName, Price , Date_
Table2 Contains following fields: ID, CusName, Price, Date_
I would like to update Price and Date Field of Table 1 where Table 1 ID matching with Table 2 and Table 2 Date is maximum(most recent date).
View 4 Replies
View Related
Aug 7, 2015
I want to update a table called tblFinalOrder, that looks like this:
In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:
My problem is that Access doesn't properly update the table how I want. I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.
Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.
Code:
UPDATE tblFinalOrder AS a
INNER JOIN tblSAP_XWP_SW AS b
ON a.SAPNr = b.sapxwpsw_sapnr
SET a.DynaCT = 1
WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);
I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.
View 3 Replies
View Related
Nov 25, 2014
How to get my query to run from criteria based on a combobox from another table. I have a field "prac" that has entries such as JF, MG, PM, RJ etc.
My query has the criteria for prac as [Forms]![PracReportPrint]![PracChoice].
PracChoice is the name of the combobox.
If I specify a valuelist for the combobox the query works. I created a different table just for a list of practitioners that contains only an index key and practitioners (JF, MG, PM, RJ, and so forth). When I set the combobox to table/query and select practitioners from my other table I get the correct list,b ut the query doesn't seem to detect what is selected from the combobox.
View 2 Replies
View Related
Apr 1, 2013
I am trying to create a Delete query.
I am trying to delete a specific part from multiple BOMs in my database.
I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.
So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.
I am able to select the records with:
Code:
SELECT SYSADM_REQUIREMENT.*
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));
Now how do I delete these same records.
I am getting error saying I have to select a table to delete from....
View 11 Replies
View Related
May 20, 2014
I am trying to write a query that gets all the telephone hours from a worker done under supervision in a town (qry_svhoursbytown). I have a table that has the workers details, including town (Crisis_support_workers), then another table that has what hours each worker has done (Supervision_Hours). The town is selected from a combo box on the form (frmSearch), under the heading �total hours by location�.
I try and select Bathurst then press recalculate results and in the text box next to supervision hours, it says 66, but if you look in the Supervision_Hours table, there is only 11 hours for the workers that are in Bathurst.
In the query, I have the sum total of the hours field in the Supervision_Hours table, the Town field from the Crisis_support_workers table with the total selected as Where and in that criteria I have [forms]![frmSearch]![ComboTown], then I just have the town field displayed.
What do I need to change to get it to sum correctly?
I have attached the database below so people can take a look.
View 1 Replies
View Related
Nov 18, 2014
how I can achieve this in Access
I have a table created in Access- "Master"
FileName Sortorder
ABC_..........4
CDE_..........2
EFG.ss1.......1
GHI.srs........3
I have a Query created in Access whose output is
FileName RowCount Exception RunDate
ABC_20141117.....10...........5........11/17/2014
CDE_11172014......23.........10.......11/17/2014
EFG.ss1................55..........0........11/17/2014
GHI.srs.................15..........5........11/17/2014
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
Required Output:
FileName RowCount Exception RunDate SortOrder
EFG.ss1...............55............0.......11/17/2014...........1
CDE_11172014.....23...........10......11/17/2014......... 2
GHI.srs................15............5.......11/17/2014..........3
ABC_20141117.......10...........5......11/17/2014..........4
View 10 Replies
View Related
Jun 3, 2013
I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.
Here are the relevant tables:
tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
tblPeopleStates is a junction table between tblPeople and tblStates.
It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.
tblPeopleRegions is a junction table between tblPeople and tblRegions.
It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.
For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.
For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).
If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).
PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]
View 8 Replies
View Related
Sep 29, 2013
I have add my two tables
Main table calls Attack
Sub table calls Research
the main table attack is linked with sub table Research by Attack.ID and Research.attack_id from form automatically
The sub table Research have more than one record which is linked into the main table Example:
How can I get the data which is marked in blue into record 24 and the data marked in Red into row 23
using Unite_Equal column?
View 3 Replies
View Related
Jul 22, 2013
Table 1:
Account Number
Start Date
End Date
Cost data**
Budget data**
Table 2 (Imported excel file with cost/budget data):
Account Number
Cost data**
Budget data**
Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.
So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.
View 4 Replies
View Related
Nov 17, 2014
I have a combox with the following query in its RowSource: SELECT [Error Codes].[ID], [Error Codes].[ErrorCode], [Error Codes].[Active] FROM [Error Codes] ORDER BY [Error Codes].[ErrorCode];
I now need to change the query's Rowsource via code and so far I have figured out that this works:
Code : Forms![frmAcceptReturn].Form.cmbErrorCodes.RowSource = "Select ID, ErrorCode FROM [Error Codes Missing]"
However, the table "Error Codes Missing" also has a field called "Active" which I need to take into account; If the error code is not active then it should not show up in the combobox.
I know how to do that in the original RowSource, but how do I do it with code?
View 9 Replies
View Related
Mar 28, 2014
In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).
View 1 Replies
View Related
May 6, 2013
I would like to have a table update query populate cells in a field, based on an IIf statement (below), but states that the IIf statement arguments still need to be enclosed in parentheses. I don't understand what the problem is, I have two sets of opening / closing parentheses.
Code:
IIf IsNull (=Mid( [CONTRACTOR_TASK]![TASK_NAME] ,6,6))
View 3 Replies
View Related
Apr 24, 2013
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
View 7 Replies
View Related
Jan 4, 2014
WinXPPro Sp3
Access 2007
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code:
SELECT Unique_No, Table_Name, List_Order FROM My_Table
WHERE Table_Name = 'Titles'
AND List_Order IN (3,1,15,4,5,12,7,2)
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
List_Order
3
1
15
5
4
12
2
7
View 3 Replies
View Related
Aug 4, 2015
I am having a problem to get my query to run properly. I have a huge IIf statement that doesn't seem to be working but I am not sure what is wrong with it. What it is supposed to do is return a good, actionable, or poor based on a specified weight and an actual weight. So what the query does is pull information from a table based on the specified weight and then using that information it should give back the correct rating in the last column.
Here are the conditions:
Poor:
AW(Actionable Weight)<Lower Actionable Weight
AW> Upper Actionable Weight
Actionable:
Lower Actionable<AW<Lower Acceptable
Upper Acceptable<AW< Upper Actionable
Good:
Lower Acceptable<AW<Upper Acceptable
The lower acceptable, actionable, and upper acceptable, actionable are different numbers based on the specified weight entered.
Here is the IIf Statement I currently have and I wasnt sure if order mattered or what I was doing wrong..
Rating: IIf([LOWER ACTOINABLE]<[Actual Weight]<[LOWER ACCEPTABLE],"Actionable",(IIf([LOWER ACCEPTABLE]<[Actual Weight]<[UPPER ACCEPTABLE],"Good",(IIf([UPPER ACCEPTABLE]<[Actual Weight]<[UPPER ACTIONABLE],"Actionable","Poor")))))
View 6 Replies
View Related
Mar 20, 2013
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies
View Related
Aug 29, 2014
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
View 5 Replies
View Related
Jan 13, 2014
Been working on this for a while now and can't get it to work how I'd like after trying a few things.
I have a form ("Production Form") from where users input start and end times for various processes against a particular product. Currently, I have (or had) a query (and linked report) "ProductionDurations" where durations for each of the processes were calculated for different product runs.
I have since tried to adapt this query to include reference values contained in another query ("ProductionDurationsPerBulkLitre", based on a "Products" table) for how long each process should take for each production, by multiplying by the volume processed. Here's what I have at the moment in the "ProductionDurations" query:
Quote:
SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration,
DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration,
DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration,
[Code] ....
Rather than returning what I require, the above bolded part is returning a calculated value for each production against each of the products. What I require is a calculated value for each production against the product that has been selected on the form.
View 14 Replies
View Related
Dec 22, 2014
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies
View Related
Feb 18, 2014
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
View 3 Replies
View Related
Jun 23, 2015
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
View 2 Replies
View Related
Sep 18, 2014
I'm trying to perform a DELETE query at run-time. The criteria for deleting records is somewhat complex due to various one-to-many dependancies between the tables, overlapping of date fields etc., so in order to restrict the DELETE to only those records which qualify, I've set up a load of smaller queries to produce the list of eligible records.There is only one parameter required - a 'CutOff' date which is arbitrarily chosen by the user and is available in a textbox control on an open form when the code is triggered.
Here is the main DELETE query :
Code:
DELETE DISTINCTROW [tblComments].*
FROM [tblComments]
INNER JOIN [qryCommentsToPurge]
ON [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
WHERE [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
I've put qryCommentsToPurge and all the cascading sub-queries below - have highlighted in red where the parameter value is required (it is used in the queries to identify the records which must be retained, i.e. not deleted)Each of the individual sub-queries works fine and correctly (they return the correct population of records that they were designed to)If I run the sub-queries manually, I am prompted to provide the CutOff date, as you would expect - and the recordset returned in each case is correct for that date.
If, however, I run the DELETE at run-time (with the form containing that textbox open and containing a valid date value) I get a 3061 error :Suggesting that the queries can't pull the parameter value from the textbox on the form. But I've stuck a watch in the debugger on [Forms]![frmArchive]![txtCutOffDate] and I'm getting the correct date value? The form is open and available at the time.So why can't the query see it? I use these kinds of control references elsewhere in other queries and they work fine. I don't understand why it's a problem here?
Code:
SELECT [tblComments].*
FROM [tblComments]
INNER JOIN [qryIssuesToPurge]
ON [tblComments].[IssueID] = [qryIssuesToPurge].[IssueID]
ORDER BY [tblComments].[CommentID];
[code]...
View 1 Replies
View Related
Sep 12, 2014
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies
View Related
