I'm tracking the holiday entitlement of a team of people. I use a query to work out how much unbooked holiday they have to take.
My problem is where I'm scheduling next year my query returns the names of those who have booked a holiday and their remaining entitelement. That's as it should be. However if someone hasn't yet booked any holidays then it simply doesn't display their record. I would like it to treat that record as zero and show the remaining entitlement as a full years entitlement.
Here's the SQL SELECT Employees.Trainer_Name, Sum([2015 Holiday].[2015 Days]) AS [SumOf2015 Days], Employees.Holiday_Days, [Employees]![Holiday_Days]-[SumOf2015 Days] AS 2015 FROM [2015 Holiday] INNER JOIN Employees ON [2015 Holiday].Trainer_Name = Employees.Trainer_Name GROUP BY Employees.Trainer_Name, Employees.Holiday_Days;
The problem here is that the Sum of 2015 holiday is Null
Hi, i have a simple query but for some reason it's not working.
Let me get to the point. 2 tables - table1(id,txtfield1) table2(userid, idfield) idfield contains id so foreign key (although hasnt been set up as that yet)
Aim: select all fields from table1 where table2 doesn't contain them.
Hello all, I have a form that looks at my "master table" where users put in general info about a sheet of material. There are two combo boxes on this form, Batch# and Lot#. They will use the same Batch# and Lot# for many sheets. Every time a new batch and lot are entered, this will bring up a pop-up form where the user needs to take a measurement of that batch and lot for QA. This will go into a different table, where the batch and lot #'s are unique(many of the same batch#'s with different Lot#'s). After the lot# is entered, I want to run a query in VB that looks for those unique identifiers, and if it doesn't find a record, my pop-up form will run. This is probably something very easy....... It was a very long weekend and I'm tired of thinking.
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010 John Doe 1006 P 21-JAN-2010 John Doe 1006 CLERK P 01-FEB-2011 John Doe 1006 F 01-MAR-2011 John Doe 1006 P 01-APR-2011 John Doe 1006 CLERK F 01-JUL-2012
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010 John Doe 1006 P 21-JAN-2010 John Doe 1006 CLERK P 01-FEB-2011 John Doe 1006 F 01-MAR-2011 John Doe 1006 P 01-APR-2011 John Doe 1006 CLERK F 01-JUL-2012
Tables join using fQSRegno. Scenario is Tbl Individual contains all the members info.Tbl Point contains point given to members who attend courses. Problem is when I created a query...
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
I have a stock-type record and a stock-item record. I want to update a flag on the stock-type record called instock to be true if there are related stock-item records and false if there are no related records. Does anyone know how to run a updating query that will do this? I am fairly new to this so I'm sorry if this is a bit of a basic question. Thank you for your help in advance.
I have a module that I am using to verify that file paths in my table point to valid jpg files. The table has grown quite a bit since I first created the module and it has gotten to the point where it's taking too long for the module to execute. I'm in the process of trying to change the record set for the module from the table to a stored query procedure but it's turning out to be a little tricky for me. When I execute the following module, I'm not getting any error code, it just doesn't seem to do anything at all. The bits in red are the parts I've changed. Before that the module executed as I expected it would.
Code:
Sub TestIt2() Dim strFileName As String Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("QryUpdateRevisionHistory")
Query_Rates (Actually this is the result of a query): Unit R_Date Sold_Rate A 24-AUG-15 145
[code]...
So what I want is a list of all the records from the Query_Rates table where the absolute differences between the sold rates between Query_Rates & [Sent till date] (matching the unit # and the dates) is greater than 1 and the record shouldn't be displayed if it is already present in the [Sent till date] table. But if you notice the first record which has unit A is already mentioned in the "Sent till date" table and shouldn't be repeated again in the query result.
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
I am trying to achieve the following - I want to query a table to see if a record exists with a particular field blank. If so, I would like to prompt the user for data.
In real world terms, when assigning an item to a user I would like to first make sure that the item is not already assigned to somebody else. I have 4 fields, UserName, Item, IssueFrom, IssueTo. So when an item is assigned to a user, the first 3 fields are populated and the IssueTo remains blank, until that item is assigned to somebody else.
At the minute I have nothing in place to prevent a user from assigning the same item to multiple users and having multiple records for the same item in the table.
I have 2 identic tables: T1 and T2 which contain only one field NR.
T1 contains the data: 1 2 3 4 5 6 7 8 9 10 11 12
T2 contains the data: 5 6 7 8 A B C D
I want to select all the records found in T1, but not found in T2. So, I wrote the following SQL query:
SELECT T1.NR FROM T1 WHERE NOT EXISTS (select T2.NR from T2);
Unfortunately, this query doesn`t return any record. And the strangest thing is that the query:
SELECT T1.NR FROM T1 WHERE NOT EXISTS (select T2.NR from T2);
have the same effect like:
SELECT * FROM T1,
I mean it returns all the records of T1. I mention that the query was written in Access. What I have to do? Can anybody help me? What solutions do I have? I need a query, not a VBA code!
I need to make a query that only selects data that is in another query/table.
I have tried to do this using a query with IN criteria as follows.
SELECT fldOne,fldTwo,fldThree, FROM tblOne WHERE (((tblOne.fldOne) In (Select fldOne from tblTwo)) AND ((tblOne.fldTwo) In (Select fldTwo from tblTwo));
But that isn't working. Any ideas as to why?
I think that the answer might be using the EXISTS reserved word, but I don't know how.
How can I do an existence test on a table in my Access MDB in a script. The job is to determine if a temp table exists, and if so, then to drop the table.
What syntax would I use to accomplish this in form event code?
Hi, I have the following problem which I hope someone can help me with:
I have a form which is used to enter data into a table. One of the fields is 'Code'. I want the form to check when data is enetered into this field is it already exists, ie if the code is already present in the 'code' field in the table, and if it is it should give a warning message, if not then continue as normal.
Any help would be greatly appreciated, thanks in advance!
I have a form used to enter new contacts. I would like to insert a query or macro that would automatically to see if the name (first and last) already exists in the table to which this form is attached. If the name exists, I want to open the existing record and add information to it. If not, I want to enter the information as a new record.
I keep getting the error message "Table TempMsysAccessObjects already exists" when I try to compact/repair my database. The only help I've found in the MS knowledge base just says to delete the table. I keep deleting it, but it keeps coming back. I've tried the decompile cmd line option, no change. Do I need to just rebuild this db, or what?
the problem is that sometimes the table doesn't exist... Instead of trapping the error code, is there a way I can check to see if the table exists? If it doesn't I'll simply skip this step of my code and move on.
I have tried the attached code from a previous post http://www.access-programmers.co.uk/forums/showthread.php?t=98727&highlight=table+exists but some of the code doesn't work on Microsoft Access Project. Does anyone have any ideas how I would amend this code so that it works?
I have created a query/report that meets all necessary criteria except that it contains multiple TYPEs per employee. This isn’t an issue unless one of the TYPEs is ‘W’. As TYPE ‘W’ supersedes all other TYPEs, any others that meet the other criteria should no longer be reflected in the query/report.
