Hi All I have a feeling that this is an absolute newbie question. I have three queries, qryVisitsDue, qryVisitsOverDue, qrySiteView. The query qrySiteView uses SiteID to produce a listing of all sites which includes a count of visits for those sites. If there is no visit data the site does not display. To display the counts I'm using the other two queries. In the first two I'm querying a table, tblVisits, to find what visits are due or overdue based on the current date. They work fine where there is visit data. My problems start when there is no data for a site. I need to be able to show the sites where there is no visit data. I'm not very experienced so basic explanations are probably the best. Any help much appreciated. ChrisD
I am currently working on a database and I'm using a form to view the records. A standard Access behavior is to show an empty record (new record) as the last one. is there a way to disable this "feature". I would like my form to display only the records, which are really filled with data and not show the blank one at the end??
I have a work order system that people use but it somewhat randomly puts blank records into my table. I've added a lot of validation checks when submitting, closing and resetting the form and limited the way people can exit out of the form to fight this issue but it still happens.
I'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code
Code:
Dim cnn As ADODB.Connection Dim recordst As ADODB.Recordset Dim strSQL As String Dim strPath As String Dim appXL As Excel.Application Dim wb As Excel.Workbook
Good afternoon! I have a database that tracks the withdraws and returns of shared tools. The withdraw form has a check box on it that is checked to show removed when the item is ordered (this keeps that item from showing up in inventory searches). When the item is returned, that check box is unchecked, showing that the item can be withdrawn again.
When a person withdraws an item their supervisors name is also logged so that we know who to chase down when the item doesn't come back.
I have a query that is supposed to show what items are still outstanding based on a date range (I got the prompt for date range to work..yea me). However, the query shows every time that the item was borrowed.
Example... I borrow a hammer and my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The next I borrow the same hammer and again my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The day after I borrow the same hammer and my supervisor is Joe. I return the hammer. It does not show as still withdrawn in my query.
Some other day I borrow the same hammer and my supervisor is Joe. I do not return the hammer. The two times I borrowed the hammer with my supervisor as Bob show up as well as the other time that I borrwed it with my supervisor as Joe shows up as well as the one that shows I still have it. . One of the criteria in the query is essentially only show the items that have the check box checked (criteria=true). But that apparently does nothing, or (more than likely), I am doing something wrong.
This is the SQL:
SELECT tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number FROM (tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number GROUP BY tblItemSpecifics.Removed, tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number HAVING (((tblItemSpecifics.Removed)=True));
I have two queries. The unique key in both queries is GUID for katalogposition.
One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.
The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.
The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.
1. No match in second query "yq_NonTermination" 2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".
I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.
How can I do that? Is there another way to do it? Please see attachment.
I am creating a query with criteria for a dates column. The column cotaining dates in my Table has many empty fields. I want to limit the criteria for my dates to >#1/1/2004#, but I don't want Access to exclude all the empty fields because I want all the data displayed for the purposes of my report.
What do I do? I'm not terribly familiar with Access, so please explain as completely as possible. Thanks in advance!
SELECT DISTINCT tblvisit.[Pen ID] FROM tblvisit WHERE (((tblvisit.[Visit Date From])<=[forms]![fRMBOOKINGAVAILABILITY]![From]) AND ((tblvisit.[Visit Date To])>=[forms]![FRMBOOKINGAVAILABILITY]![To])) OR (((tblvisit.[Visit Date From]) Between [forms]![FRMBOOKINGAVAILABILITY]![From] And [forms]![FRMBOOKINGAVAILABILITY]![to])) OR (((tblvisit.[Visit Date To]) Between [forms]![FRMBOOKINGAVAILABILITY]![From] And [forms]![FRMBOOKINGAVAILABILITY]![to]));
This successfully finds booked pens in a cattery. How though, can I get to see the pens that are free? Is there a feature that will allow me to conduct this query before conudcting another one to show the values which didn't appear? Thanks:D
Just setting up the query filters in MS Access 2007, by simply going into the design view for that particular query, setting the criteria, so really can't see how it isn't working? I put Like "*TOF*" in the criteria field and no records are returned for that, even though there's many records including that word in that field?
I am trying to create a report which basically includes the following:
Company, Wages, Contribution.
Each company reports wages for each employee every month. Then they also contribute to a general fund based on a percentage of the wages. For instance:
Each employee is required to contribute, in this example, 1% of gross wages to the general fund.
On occasion, the company does not pay in the required 1% of gross, say, for CompanyA EmployeeA, they only paid in $35.
Here is what I need to do. If any contribution amount for any employee is incorrect, I want to display all the records for that company, not just the incorrect ones. The report is grouped by Company, and may contain dozens of companies.
I am already passing a number of criteria to the report using a filter, including the date range and other fields which are informational.
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K and 5 pc set = A,B,C,D,F and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000 B=3000 C=3000 D=3000 F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
Not sure if this is possible? I have a column called Issues and another column I want to call ShortIssues and I basically want ShortIssues to be Left(Issues,50) so that its just 50 characters long of column Issues.
I have a table1 with filed "EXZ" and that field contain some data like "7.5/400/Eex de IICT4" now i would like to run an update query on that table and update field "EXZ" from "7.5/400/Ex de IICT4" to "Eex de IICT4". The last part of the data is not allways the same but it does allways start with "Ex"! So is that even posible to do with query or do i have tu use a macro or what?
All I want is to pull all the employees with their corresponding total worked hours. There are cases when some employees forget to fill their time sheets in a week and might not have entries in weekly_timesheets table. Since I want all the employees irrespective of whether they have a record in weekly_timesheets or not I am using left join.
The left join I am using is as follows:
SELECT EMPLOYEES.EMPLOYEE_ID, WEEKLY_TIMESHEETS.TOTAL_HOURS_WORKED FROM EMPLOYEES LEFT JOIN WEEKLY_TIMESHEETS ON WEEKLY_TIMESHEETS.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID AND WEEKLY_TIMESHEETS.WEEK_NO = 46;
The moment I am trying to execute this statement Access is crashing. I couldn't figure out what am I doing wrong.
I want to execute the above sql query but the result is not what i'm expecting. the field entry that i wan to extract the leftmost 14 characters is 2.005113E+19 instead of just having plain numbers like 2005113009010532000000.....
I think it's becoz of the format that's why Left function is not working as expected. Can somebody help me out regarding this? Thanks very much
I have used the left function to provide me with the left x characters of a field, which works fine.
Description: Left([c$products],[diff])
However, I want to query this field to only give me certain descrpitions, but it will not work. Can anyone let me know if this is possible, or if I'm totally out of luck,
I'm kind of a newbie to Access but am learning :D I've found myself stuck with a join statement
I have 2 tables: tblFleet & tblService
I need to find the most recent service date from tblService for each entry under tblFleet....
This is my code thus far, but it shows all the records for both tables where the fleet_Num field is equal....
SELECT tblFleet.Fleet_Num, tblFleet.In_Yard, tblService.SrvDate FROM tblFleet LEFT JOIN tblService ON tblFleet.Fleet_Num = tblService.Fleet_Num ORDER BY tblService.SrvDate DESC;
SELECT tblEmployees.EmployeeID, tblEmployees.LicenceNo, tblTimeSheet.ShiftDate, tblEmployees.FirstName, tblEmployees.LastName FROM tblEmployees LEFT JOIN tblTimeSheet ON tblEmployees.EmployeeID = tblTimeSheet.EmployeeID WHERE (((tblTimeSheet.EmployeeID) Is Null));
errrr nope it doesnt work.. and that is direct from the wizard...
I have a work around but it is inelegant not to mention not workable..
what i need to do is filter on a shift date as i need to see what employees are not rostered on particular dates..
what i have had to do is make a query selecting base data from timesheet and filter the date at that point... make another query to do the left join.. again not elegant... to make a report i would somehow have to programmatically adjust the filter on the date.. too hard basket and will be too slow when i have a copious amount of data in 12 months... anyone know what the hell is wrong?
I have an imported table with zipcodes, some are 5 digit some are 9. When i go to build my query i dont get an accurate number, i tried a left function to get the 1st 5 digits but keep getting a invalid syntax. I am in the query builder. anyone help me with syntax, heres what i currently have
I have a loan db that I want to write a query to reflect all active loans with or without a current month's payments. In other words, I want it to select all active loans even if there was no activety during the month. I have 1 query that pulls out all active loans; qryActiveLoans. I then create a left join (2) with that query Grouping on the Loan# and summing the Principal and Interest fields where the payment Date is between #10/01/07# and #10/31/07#. I need to group on loan the number because some loans have more than one transaction in the month.
The problem is that the query does not select the loans that didn't have any payments during the month. I thought that a left join (2) will pull out ALL the records from the qryActiveLoans query. I've tried adding the word ALL after the SELECT statement but that does not work. I've also tried various forms of nz without success. This is probably a common problem but I could not find it in my searches of past postings.
I've created a db for my mortage company and I have everything complete except returning customers. I want the customers to have the same customerID but different TransacionID. Sounds simple to me but im having problems. It could be a table issue but im hoping its just a form thing.
I have tables: Customers-CustomerID(autonumber),CustFirstName,CustLastName, PhoneNumber,EmailAddress,OriginatorID.
Then I have Table: Detail- CustomerId(lookup from customer table),TransactionId(autonumber),SSN,Address,City, State,Zip,Spoucename,CreditScore,
Then I have Table: Loan- TransactionId(lookup from Detail),LoanType,LoanAmount,StartDate,EndDate, LoanLender
I have a form for new customers which is the customer table. Then once the customer has his credit checked we enter the customer details with a form. Then when they want to move forword with a loan there is a form that pulls the TransactionID from the Detail Table.
I cant figure a way to make a form that will create a new transactionID associated to the custs CustomerID without overwritting the older record.
Hi, when I type 0600 in a text box, it dispays it as 600. How do I make it so it displays the number that I actually type in, with a zero as the first digit? Please help. Thank you.
