I currently have a query (see code below) showing me a total count of WorkUnits. I would like to exclude WorkUnits if the PossibleCause field is Out of Stock. When I add criteria to Where in the code I keep getting an invalid bracketing issue and I cannot solve it no matter what I have tried. Any advice on how to add the critieria above to this query correctly?
SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals]
FROM [Select Distinct [WorkUnit]
FROM WorkUnitsFaultsMainTBL
WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
[TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt]
AND
[Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;
Hi, I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 x x (check boxes) Y ACD 2 x x
Query 2. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 10/8/05 (query performs Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
Can i save an SQL statement to an existing Query. what i would like to do is have a crosstab on fields that will be determined at runtime. so i would have the pivot on a field that is chosen at runtime. I can run a sub that will generate the correct SQL, i am just lost on how to save it to an existing query.
the full blown explanation of what i am try to do, is the following. i have a report which is going to use a cross tab. so i need to attach a query to the report. if i assign report.recordsource = SQL i get a message that a crosstab can't be attached to the report. but if i use a saved crosstab and i write report.recordsource = me.myCrosstabQry then it works. don't know why, but it does.
is there anyway to view records in an access database from an existing database? i have 3 databases that perform basically the same things, but are for different people... i would like to create a database that can report all this information in one spot, instead of creating reports in every database. if this is not possible, i'll probably go the asp.net route, but this seems to be an easier way, if it is possible what do u think? *j
I’m trying to modify an existing SQL query that calculates actual working days between start date in tblECN and end date in tblECNDetail to do the same between start date in tblECNDetail and end date in tblECNDetail (same table).
Where I’ve gotten into a bind is with the joins of the two tables that are needed in the first query but not in the second as both fields come from the same query.
In the current working query I join about tblECN.ECNID and tblECNDetail.ECNID. In the new query I don’t think I need to create these joins at all as BOMEntryStart and BOMEntryEnd both come from the same table (tblECNDetail). I’ve bolded all references to the table no longer required for join but I don’t know which join statements I can be rid of. When trying to save I get an error in the FROM statement if I leave as is. If I get rid of the join statement I get an error
working query SELECT [tblECN].[ECNID], [tblECN].[RelDate], [tblECNDetail].[PendDate], DateDiff("d",[tblECN].[RelDate],[tblECNDetail].[PendDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[PendDate],2)>5,5-Weekday([tblECNDetail].[PendDate],2),0)+IIf(Weekday([tblECN].[RelDate],2)=6,1,0)-IIf(Weekday([tblECN].[RelDate],2)>Weekday([tblECNDetail].[PendDate],2),2,0)+IIf([tblECN].[RelDate]=[tblECNDetail].[PendDate],1,0) AS weekdays, Query5.HCOUNT FROM (tblECN INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) LEFT JOIN [SELECT Query4.[tblECN].[ECNID], Sum(Query4.TEMPVAL) AS HCOUNT FROM (SELECT [tblECN].[ECNID], 1 AS TEMPVAL FROM tblHoliday, [tblECN] INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID] WHERE (((tblHoliday.Holiday) Between [tblECN].[RelDate] And [tblECNDetail].[PendDate]))) AS Query4 GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID] WHERE ((([tblECNDetail].[PendDate]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECN].[DoNotProcess])<>"Do Not Process"));
Not working query SELECT [tblECN].[ECNID], [tblECNDetail]., [tblECNDetail].[BOMEntryEnd], DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5,5-Weekday([tblECNDetail].[BOMEntryEnd],2),0)+IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)-IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)+IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays, Query5.HCOUNT FROM ([b]tblECN INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) LEFT JOIN [SELECT Query4.[tblECN].[ECNID], Sum(Query4.TEMPVAL) AS HCOUNT FROM (SELECT [tblECN].[ECNID], 1 AS TEMPVAL FROM tblHoliday, [tblECN] INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID] WHERE (((tblHoliday.Holiday) Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]))) AS Query4 GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID] WHERE ((([tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECN].[DoNotProcess])<>"Do Not Process"));
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
I've got a query that takes a part number, strips off the un-needed prefixes and suffixes, and gives me just the meat & potatoes of what I need.
I'd like to insert these results into a particular column in an existing table. Say the column name is Part_Number and the table name is CompletedWork. What would the SQL look like for that? I think this is relatively simple but my SQL skills would never be found in the same sentence as 'good'.
TIA for the help. This forum is an incredible source of information.
Wow, seems like this is my favorite place in the world now.
My problem is that I need to append data from one table to another, on the condition that any of the fields in the record have changed since the last time the query ran.
For example,
tblEmployee has the fields 'EmployeeID', 'Name', 'Position', and 'Office'. We are appending to tblEmployeeHistory with the same fields in addition to a field for a date. When executed, the query should append the records where something has changed and attach today's date to them. This enables us to later use another query to call up old information based on the date.
Once again, this is a hypothetical situation and not my actual database. Any solution or comments are welcome.
I am in the process of making a database and basically I have everything done but this saving, loading and adding/removing part.
Would it be possible for me to allow end-users to run queries that they could save, load, add to the existing table or remove from the existing table?
When I say Add/Remove what I mean is could a user of the database run a search of every customer in the state and then remove everyone in County X or on the other hand could they run a search of every customer in the state and then add every customer in a neighboring state.
Kinda like layers and again I have all of the criteria set up I just need the Save, Load and Add/Remove buttons on the form. I should add that I do not want users to save over the main table I want them to save it as there own text file they can upload later.
Folks, can someone help me on this? I am not sure why the select query inside this procedure is not working. Here is the that routine:
Public Sub temp() Dim strDocName As String Dim strTableName As String Dim strTbl As String Dim aot As Access.AccessObject Dim strSQL As String Dim rpt As Report Set rpt = CreateReport
strTbl = Forms!frmSearchBoilerGuar!cboTypeOfGuar
For Each aot In CurrentData.AllTables If aot.Name = "strTbl" Then strTableName = strTbl End If
Basically, I am trying to select a table name from the combobox and then use that table name for my query. Then I want to use that query as a recordsource for my report.
i'm a Access novice. I have tables with existing data in numerical form, and would like to know how I can use Queries or VBA code to update these values into a new format in a new table. for example the original data might be of race type:
1 - caucasian 2 - african american 3 - hispanic 4 - other
how can i achieve this in Access? i know how i can do this conceptually with "if" and "case" statements, but I have no idea how i can do this in Access. I don't want to mess with the original data, so please help with CODE or QUERY examples.
I am looking for a way to add a calculated field to the end of an existing query using VBA. Is there an easy way to do this?
The data I receive from an external supplier shows monthly data split by column with a new column added in each month. I then need to reflect this by adding a new column to the end of the query. It is currently a manual tweak, but I want to automate this with code.
I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".
A visual of my tables:
tblLabels (Main table) - LabelID - BaseProduct (old field with text data) - LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)
tblBaseProduct (new table) - BaseProductID (PK and FK to tblLabels) - BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)
I tried to design a query using design view of the query design and this is what I have:
Code:
UPDATE tblLabels, tblBaseProduct SET tblLabels.LabelBaseProduct = [baseProductID] WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));
When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database.
I have a table linked to SQL Server 2014. As SQL Server 2014 does not support calculated fields I created a query to use formulas. Now I want to write formulas on the existing fields ( TotalMarks ) of table Not to create new fields.
-Existing Access Database contains tables with 1-2 million records
I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].
In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.
Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].
I'm writing a database for a record label. Here is the issue. I need to calculate total number of CD's sold for a specific state, and CD Name. I have a query that asks for the name of the state, and CD Name, it then produces the total number of records from this result. This works fine.
My problem is that I need to use something like a DSUM on the total number of CD Sold for the this given result. I have tried to use the previous query as a Make-Query, and then using the generated table in my next query. However, this has not worked, it just reproduces the old results with out tallying them up into one general record.
Is there anyway to use one query's results in another query.
I have a database used to manage teaching assignments (which kid is assigned to which teacher so to speak). I have this relationship defined through three tables, a teacher table, a student table, both with unique ID's. The third table is used to define the assignment. Also, the kid table has an extra GroupID. The group ID is what is used to define. So in the definition table, Teacher 1 is assigned to Group 1, and so on (though their may be 20 kids in group 1). When a new teacher is added to the teacher table, I need it to add it to the corresponding field in the definition table. The groupID is in the table as an Autonumber so that will populate automatically.
I need to appendTeacherID to tblassignment (TeacherassignmentID) and have only one occurrence of the TeacherID. So, if I have teachers 1-8 listed, each assigned to a group# in the tblassignment, and I add Teacher 9, I need it to add Teacher 9's unique ID to the TeacherassignmentID field without adding 1-8 again. I can't figure out how to "check" for ID's 1-9 and add only those I've added to the teacher list that aren't already assigned to a group.
I've tried a few different SQL queries append/select queries but nothing seems to do what I need it to do....
Can I use the append query to update a table by replacing all existing information with updated information while at the same time adding all data that isn't currently in the table?
Right now every time I run the Append Query it just adds the same information as a new row instead of replacing the existing row with the updated information.
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.
I am trying to use a single record and cell of data generated from a query as criteria in another query but can't figure out how to do it? Is there a way to reference a query field in the criteria in design view of another query?
Details:
I have a table of data for each month with supplier codes in each table, but no date field.
I am trying to build a query that will automatically pick the most recent month of data. I have built a query that uses an iif statement in SQL assigning a number to the most recent month of data and then I am using the max filter in that query to show the single highest number (But this logic misses suppliers some suppliers but does assign a number to the most recent set of data). In this case its 2 for february data. I want to use this digit to filter a seperate criteria that will show me all the supplier codes for the month of february. One problem is that some suppliers are in every month others, come and go throughout the year, so I have to assign this criteria for each month. Therefore I want to use the 2 from the first query and plug that into each criteria section under each month field of the second query.
The other option that I can think of but can't seem to find a fix, is to merge all 12 tables so there are duplicate entries and can have a date or number assigned for each month that the supplier code shows up, then just use the max number. I can't seem to find a way to add all the codes creating duplicates into one field. I used a union query but there are no duplicates with this method thus foiling my plan.
HI all, i have a really annoying proble where the solution is probably staring me straight in the face. Basically i want to produce a report where the user is prompted to enter months to be looked at within a certain period and also the years, in my query i am using:
Between [First month] And [Last month]
and
Between [First year] And [to end year]
when i do this the query just asks for the months and doesnt go on to ask for the years...help?! :D
