It's unknown for me why Access hasn't got MINUS implemented and Unmatched Query Wizard doesn't help me much. So I'm wondered if anyone had to confront the following task:
Table1
Book Agent
----------
b1 a1
b2 a2
b3 a3
Table2
Book Agent
----------
b1 a1
b3 a3
The problem is I have to eliminate duplicates but I need to find unique row, which is Book and Agent. From my example it's row with (b2 a2). I tried using
Select * From Table1
Where (Book, Agent) Not In (Select Book, Agent From Table2)
didn't work
I also tried EXISTS
didn't work
Unmatched Wizard compares only one field, not two.
I am setting up a query to display a records of products within certain sizes.
I want the width to be input, eg; "500" and i want all records - plus or minus 50 of this record to be displayed. I know i could use the "BETWEEN" "AND" command but i want the user to only enter 1 size if it is possible.
Hi This is probaly really easy, I want to identify just minus figues in a query ie -5.00. I have tried iif([TotalPrice])<0,[TotalPrice]) this shows no result. Can any one advise. Thanks in advance.
Hi, I've searched this forum and can't quite get what I need. I have a subform on a form, in the footer of the subform I total an amount owing field. Then I get the value from the footer control to the main form. I know that the total should be 0 but on the main form I get a -0.0119. How can I get this to show 0. Thanks
I have 4 tables, 2 entities, 1 relationship and 1 lookup
Entities are tblOrg and tblForm Relationship is tblVentureParticipation Lookup is tblInfo (Has many different lookups)
When an organization (these are listed in tblOrg) turns in a form, a new record is inserted into tblForm with the ID of that organization and the ID of the form
I want to identify the forms that have NOT been turned in.
So I figured
Step 1: Identify all organizations who are participating (WHERE tblOrg.OrgID = tblVentureParticipation.OrgID)
Step 2: Join all of the previously selected organizations with all of the forms (FROM tblOrg, tblInfo WHERE tblInfo.LookupID LIKE 'F##')
Step 3: Remove all of the entries in tblForm that match the result of Step 2.
I have verified that both of these individual queries return the right information ...
SELECT [qry_1].Month, B_Division_Group.Grouping_Name, Sum([qry_1].Month_Client_Count_from_B) AS Monthly_Count, Sum([qry_1].Month_Assets_from_B) AS Monthly_Total, Sum([qry_1].YTD_Client_Count_from_B) AS Yearly_Count, Sum([qry_1].YTD_Assets_from_B) AS Yearly_Total FROM ([qry_1] INNER JOIN tbl_branch ON [qry_1].BranchCode = tbl_branch.BranchID) INNER JOIN B_Division_Group ON tbl_branch.BranchName = BDivision_Group.N_Br GROUP BY [qry_1].Month, B_Division_Group.Grouping_Name;
While all monthly and yearly values were positive, the query produced exactly the results expected (i.e. one row of data for each Grouping Name/Month combination, containing the overall totals for each field).
Now, however, some Grouping Names have minus values and the query is showing an extra row (one for positive values, one for negative). It's as if having one or more minus values is being treated as a new Grouping Name/Month combination.
Is Sum() the correct method to use, when dealing with negative values, or should I be using some other function?
I am creating a report and I need data about parts that have not been used in the past 12 months. I'm not sure how to get just numbers from the past 12 months. How do I get the current date minus 1 year?
I have a datasheet subform that is acting like a timesheet. I have a TimeIn field and TimeOut field (both fields formatted as Medium Time); and a field for Hours to total the time. I used the expression =HoursAndMinutes([TimeOut]-[TimeIn]) that I found in another post, and it didn't work. All I got was #Name. Any thoughts as to why that's happening? I would preferably like the hours to total in tenths of hours, ie. 10.5.
So I am trying to setup a default value in the tbl design. The Following two fields are the two in question(StartDate & ReportDate). It already has relevant dates properly formatted in the StartDate Field. The ReportDate will always be the day before or minus 1 day from the Startdate. My question is that I can't seem to get the syntax right to make it work.
I have a report which is a statement. I want to be able to have a field that calculates rent, and minus payments. There are a few rent types, Pro Rated rent, Rent brought forward and rent changed, then there is 1 payment type. I am trying to write the sum a few ways, but have not had success. If rent type i= 1 or 2 or 3 I want it to add, if rent total = 4 I want it to minus.
=Sum(IIf([RentType]=1 Or 2 Or 3,[RentAmount],0))-Sum(IIf([RentType]=4,[RentAmount]))
I'm working on a form that I want to insert a plus button/minus button.I am aware of the vba code numbers 107 and 109 if you want to use the actual keys but I would rather use a button.
I made a button and here is the code I used now. This code is numeric box specific. So it only adds one to the MileageStart numeric field. (Additionally you can put a minus in to subtract one).
Private Sub Command153_Click() MileageStart = MileageStart + 1 End Sub
Instead of having a bunch of these buttons all over boxes on my form is there some code that I can use to "select" a text box and just have the Plus/Minus button on the form header that I would hit and it would add or subtract (and then I could go to another box select it and hit Plus/Minus and so forth).
I am trying to make a simple form to tally my total products ordered.
So I wanna add a plus and minus button on my continguous form, but i cant get it to work. How do i update the current record when clicking on the button next to the field ..? It seems only the first record gets updated ..
I am trying to limit the results of a SQL query by date. I would like to take the current date from the system clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit the results in my Query. If the current date minus one day happens to land on a weekend, the date picked should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .
Here is my SQL query right now.
SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1)) ORDER BY (Mid(ClientDiv.Client_Division,1,3));
It limits what it selects by using the current Date minus one day.
I need to skip over Saturdays, Sundays and Holidays.
I have been refreshing myself with SQL on Access 2002. All was going well until I got to the BETWEEN...AND operator. Using the Northwind sample database I was writing the query on the employee table to select all the columns between 'Fuller' and 'King' :
SELECT * FROM Employees WHERE LastName BETWEEN 'Fuller' AND 'King'
However this only brings back the two rows for 'Fuller' and 'King' whereas I would expect at least 5 rows or more depending on whether access would count 'Fuller' and 'King' as inclusive. Have had a look on the microsoft office help page but seem to be doing everything correct so I am wondering why it still doesn't work? No doubt simple but its concealed in mud at the moment. Any help apreciated.
I am new to this forum, so forgive me if this question has been asked before
I needed exact information about the way the 'like' operator works, so I pressed F1, typed 'like' in the "Answer Wizard" and was told to "rephrase my question". :eek:
The index was slightly more helpful as it obviously contains 2 topics with 'like_operator', but choosing them does not reveal anything. :confused:
Where can I find info about such things? I also need to use an operator called 'contains' which I have used when working with SAS, but appearently it does not exist in Acccess or it has another name. Has anybody heard about it?
I have an invoice-line table with records for every line on invoices. I'm trying to return all the lines associated with certain invoices, excluding all lines with "FREIGHT" in a line item field. I created a query that lists all the invoice numbers for which I want to display invoice lines for. I created a new query with the invoice line table linked to the invoice number query by invoice number (a one to many relationship). There are about 1400 results from this query. By making the criteria of the line item field "FREIGHT" and running the query, I can see that there are 81 "FREIGHT" records that I ultimately want to exclude. However, by typing NOT "FREIGHT," it appears that not only are the "FREIGHT" line item records excluded, but also records with a blank (null)line item field are excluded, and I end up with about 650 results. What's going on here? Why is the NOT "FREIGHT" critera also excluding null records? I don't have any other criteria specified in the query. :confused:
I have an expression that I'm trying to build into a totaling field in a report. There are two types of items being tallied into this total; 1 is a flat amount, the other is a calculation of # of men * # hours * rate. I already have an expression that works for the first level of calculating:
=IIf([Type]="Lawn Maintenance",[LMAmt],[# hours]*[# men]*35) Note: [LMAmt] holds a flat amount
I wanted to add, after the [Type]="Lawn Maintenance",[LMAmt] an "or" somehow which would basically state or [Type]="Dumping Charge",[Expenses] Note: [Expenses] is also a flat amount and all other [Type] codes are determined using the calculation below:
if [Type] is not = to "Lawn Maintenance" or = to "Dumping Charge", THEN [#hours]*[# men]*35
