Hi all,
I have a table with some records:
Name, Date (Day/Month/Year)
John, 09/09/2005
Phil, 10/09/2005
Xena, 03/11/2004
I want my query to display all records with today's date, or those that are 100 days older than today's date.
I have the following so far (in my query criteria box):
Date() Or <Date()-100
BUT this shows me Xena's record, which is more than 100 days old (it's 2004)
Any help/corrections are welcome
Thanks
Hello all,
I am a bit stuck in my query. I need to setup a Next Follow-up date based on the last follow-up date excluding weekends.
For example: Last Follow-up: 1/10/08.
Next follow-up should be 5 days after that. So it should be: 01/17/08.
I have a calculated field that calculates the number of days (Counting off weekends) but I can't seem to have the date setup correctly.
Thanks
Hi,
This is something simple, and I think it is my syntax that is wrong
I am building a holiday database that will be used for more than 1 year and want to produce reports only for the current Holiday year.
My method of doing this is to have a table that contains ALL holiday data and use a make table query to produce the current holiday years data for the reports to be diven from.
The problem is I want to be able to allow users to change the current year by updating 2 fields in a table with only 1 record - so have used:
Between ([Tbl_Holiday_Year]![Holiday_Year_ Start_Date]) And ([Tbl_Holiday_Year]![Holiday_ Year_Finish_Date])
as the select criteria for the query to filter only this year holidays, now the problem - when the query is run I get pop ups asking to input the start and finish dates instead reading the dates from the table, if I replace with actual dates in the criteria it does what I want it to.
I need it this way as I don't want users having to amend queries for each new holiday year.
Any ideas???
My issue is that I am trying to update a date field. When I do the date field may have a date or may be a null. When I try to pass in a NULL date with no quotes, I get a syntax error. When I have single quotes in the statement and a null value is passed in, I get an invalid use of date.
Dim DENIEDDATE1 As Date
If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
DENIEDDATE1 = Null
Else
DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
End If
update table1 set table1.denieddate = " & denieddate1 & " 'get Update syntax error with this statement
update table1 set table1.denieddate = '" & denieddate1 & "' 'fails due to invalid use of null
Hi All,
I'm trying to extract records that start with 10 but I need to exclude all that start with 1011,1015 & 1025
so far I can only extract records that I need but cant exlude the others when I use the SQL below
INSERT INTO tblConceptOrders ( TA_TASK_ID )
SELECT dbo_F_TASKS.TA_TASK_ID
FROM dbo_F_TASKS
GROUP BY dbo_F_TASKS.TA_TASK_ID
HAVING (((dbo_F_TASKS.TA_TASK_ID) Like "10*"))
ORDER BY dbo_F_TASKS.TA_TASK_ID;
I'm looking for a better way to exclude the records I don't want & collect the ones that I do
Any help will be appreciated
thanks :)
Good morning,
I'm trying to create a query and I'm asking for specific records, but also need to exclude certain records within that criteria. Here is my Select statement:
SELECT [MD IMPORT PURGE PEND].PROC,
[MD IMPORT PURGE PEND].SEG,
[MD IMPORT PURGE PEND].NAME,
[MD IMPORT PURGE PEND].GROUP,
[MD IMPORT PURGE PEND].BEN,
[MD IMPORT PURGE PEND].DATE,
[MD IMPORT PURGE PEND].RISK,
[MD IMPORT PURGE PEND].REASON,
[MD IMPORT PURGE PEND].PLAN,
[MD IMPORT PURGE PEND].PAR,
[MD IMPORT PURGE PEND].IND,
[MD IMPORT PURGE PEND].REC,
[MD IMPORT PURGE PEND].SUBNO,
[MD IMPORT PURGE PEND].CLIENT,
[MD IMPORT PURGE PEND].CLAIM,
[MD IMPORT PURGE PEND].FSDT,
[MD IMPORT PURGE PEND].PDDT,
[MD IMPORT PURGE PEND].CHECK,
[MD IMPORT PURGE PEND].PROV,
[MD IMPORT PURGE PEND].SOURCE,
[MD IMPORT PURGE PEND].PAYEE,
[MD IMPORT PURGE PEND].EFF2,
[MD IMPORT PURGE PEND].CSHRCNO,
([CARR] & [CARR1] & [CARR2]) AS CPL,
[MD IMPORT PURGE PEND].CLASS,
[MD IMPORT PURGE PEND].MKTSEG,
[MD IMPORT PURGE PEND].PAID
FROM [MD IMPORT PURGE PEND]
WHERE ((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND
(([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND
(([MD IMPORT PURGE PEND].REASON)="35") AND
(([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND
(([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND
(([MD IMPORT PURGE PEND].REASON)="35") AND
(([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND
(([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND
(([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND
(([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND
(([MD IMPORT PURGE PEND].REASON)="35") AND
(([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND
(([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].REASON)<>"35" And ([MD IMPORT PURGE PEND].REASON)<>"44" And ([MD IMPORT PURGE PEND].REASON)<>"51") AND
(([MD IMPORT PURGE PEND].IND)="HOMES") AND
(([MD IMPORT PURGE PEND].CHECK)="000000000") AND
(([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M") AND
(([MD IMPORT PURGE PEND].EFF2)="3") AND
(([MD IMPORT PURGE PEND].CSHRCNO) Is Null));
The part in bold is the exclusion. I want it to exclude records where the reason is 35 or 44 or 51, IND is HOMES, check number is 000000000, payee is s or m, eff2 is 3 AND cshrcno is null.
Can anyone tell me how to achieve this? I really appreciate your help!
Hello, I'm new to the forum and relatively new to Access. There's something I'm trying to do but I'm not sure if it's even possible so I'm hoping someone can help me out and point me in the right direction
the situation:
I have one very big table containing data for around 250 petrol stations. The data is collected monthly, so each site has a record relating to each month. E.g site A has data for month 1, 2, 3 etc.
The problem is that in any month, some of this data needs to be excluded from analysis. And this will vary over the months. What I want to do is run a query where specific records are excluded, for example, exclude Site A data for month 1 and 3, Site B data for month 2 etc.
I have another table containing a list of which site + month data is bad and to be excluded. Ideally I want to simply add to the list each month before I run the select query.
the question:
Is there any way to exclude records from a select query based on their details appearing on another table?
help would be much appreciated!
thanks
Catherine
Could someone please help me? I am trying to run a query in which I would like to exclude a particular person's name from the query. For example, I am entering productivity information for each employee and manager each day, however, I only want the employee information to appear in the reports each day. Is there anyway of excluding a name from a query?
Thanks:)
Dear Friends,
I have one query based on code, rate, quantity. I created field for total (rate x quantity). What expression i can write that the query will calculate total only with the first figure as per code and if code repeats again, should not calculation total.
Please see the following query structure. Thats how i want my total to be calculated.
Code, Rate, Quantity, total
50,100,10,1000
50,100,10
60,200,20,4000
60,200,20
60,200,20
70,200,30,6000
Thanks
mithan
I have one table that will consists of about 1,000 rows. I have another table that consists of product codes that we want to exclude from the report. I know how to include the two tables by joining, but I want to EXCLUDE the 2nd table of product codes. Basically, if any of the product codes listed on table 2 are on table 1, I don't want them to appear. So how can I do an exclude function in a query?
View 3 Replies View RelatedIs there a way that in a query from several tables you can choose to exclude duplicates of a specific field?
In the query I'm trying to create I am going through and counting the number of customers in a certain time frame. I don't want to count a customer each time they buy something though, I only want to count them once. The dates of their purchases are held in a seperate table for purchases and the customer information is held in a customer table. The information I need is in both of these tables and each customer has an ID number which is how the two tables are joined.
When I tried using the DISTINCT code it still brought in all the purchases because the customer bought things on different dates. The only way I have ever used this, however, is to write SELECT DISTINCT at the beginning of the SQL instead of just SELECT. If there's another way to use it to do what I need I will try it as long as you tell me what it is.
How can I set up the query to bring in all the information I need but not duplicate the ID numbers?
Thanks in advance for any help I get!!!
Hello,
I have 2 tables.
1) Table A, which is a table of all 100,000 users
2) Table B, which is a table of all other users
I have a query qryA, which looks for everyone in table A with a certain type of job title.
I want to exclude anyone who works for a company that occurs in the table B from showing up in the qryA query.
Any ideas?
Hi All,
Say you've got 2 tables and you want to extract data from table 1 but exclude all records from table 1 which have a field value existing in table 2?
Table 1 fields:
Test_Code
Test_Desc
Table 2 fields:
Test_Code
Therefore, I would like to query out from Table 1 all Test_Code values which do not exist in Table 2. Any ideas please?
I have designed an invoice for a project that shows the amount of money needed to be paid, some of this has already been paid and some hasnt, how am i able to make it so that the values that have been paid and therefore have been checked are not include in the query. please help.
View 1 Replies View RelatedI created a report where is lists sales volume by day. I was asked to create a table where data can be excluded from this report. The exclusion table consists of only three fields:
Date Department and a check box
What do I need to do in the query to have it exclude the data listed in this exclusion table. The table only holds data we want excluded, not all dates and departments.
I originally added the table to the source query and made the criteria "False" for the check box but when the query runs it does return any data at all.
Please help!
Hi all, I know this is a real easy one, but I am not the smartest when it comes to access. Can you help me out.
I am running a crosstab query to count and sum records in my database. I have a fied called "Amount". In my form the user is not always required to enter an amount. When I run the query, I would like the results to exclude the records that have a null value or $0.00 in the "Amount" field.
How do I format the query to exclude those records?
Thanks in advance.
I have created a query showing locations of a company's facilities, and product groups associated with that facility. Unfortunately the the products groups are located in one column and, if the facility produces more than one product, the product group is divided by a |, as shown below:
CABLE TRAY SECTION [05CT]|STEEL CONDUIT AND ELECTRICAL METALLIC TUBING SECTION [05RN]
In my query, I only want to show the product code, which I resolved by creating another table and associating the code with the product group. However, this query will exclude those plants that produce multiple products. How can I show just the product codes for all the facilities, including those with multiple product groups?
In otherwords, I would like the above example to look like this: 05CT|05RN (or something similar to this).
What expression do i use to count the number of patientID when there are duplicates (ie patient has come back for more than one visit but I just want to know the number of patients who visited (not how many times))
Count([patientID]) doesn't exclude duplicates
Hi,
Got the following question.
I have got two tables with the following values
Table X with Field ULX:
XAUUSD
XAGUSD
CAD
AAD
Table Y with Field ULY
AUDUSD
XPT
XAU
XAG
XAUUSD
XAGUSD
Now I want to select the values from table X that correspond witht the values in Table Y for which I use the INNER JOIN operation, wich is working and gives me the results I am looking for.
But now I want to select the values from Table Y that do NOT correspond with the values in Table X, when I rewrite the INNER JOIN operation but now with the <> operator, my results still show the values XAUUSD and XAGUSD from table X.
This is my query:
Select distinct Y.ULY from Y
Inner join X on Y.ULY <> X.ULX ;
What am I doing wrong?
Thanks.
How can I run a query on the table attached to show the number of UR's that were operated on. As you can see some of the UR have more than two procedure dates so I want to exclude duplicates. i have tried "count" but that just gives a "1" value for each UR on each procedure date
View 1 Replies View RelatedI've composed a table in Access 2010 that I want to use as an annex to a printed document. However, each time I try to print out a hard copy it includes the table title and the date of printing. How do I stop this?
View 2 Replies View RelatedI have a table with the following fields:
Customer
Month
Qty
Unit Price
Extended Price
I want to know, by month, what the total extended price is for all customers. But some customers appear more than once in a month. So I also want to know the total extended price for the month, but I only want to include the extended price for each customer once, using only the record with the highest unit price (or extended price, qty for each customer is consistent), and ignoring additional records with the same customer in that month.
I am an excel user, and I could quickly do this with pivot tables... but I have too many records for Excel to handle this efficiently (it just keeps crashing!), so I thought I'd move to access... but I don't know how to use access! So I may need a little hand-holding here.
I am building a query with a column of data, 'location number'.
I need to exclude any data where the location number is found in a pre-built table.
i.e. return all data except when the location number is found in the table.
Hello all,
I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.
I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.
My table has about thirty fields, the query I've produced is this:
SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts],
<snip, all other fields in table>
FROM AH_orphans
WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));
What I'm hoping for is the subset which match the following conditions:
- ESP field is not "Y". Possible values Y, N, null
- FRU field is not "N". Possible values Y, N, null
- Only those records from EntitlementName which include the substring 'uptime'
- Only those records from Account which start with the subtring nation
- Only those records from Type which include the substring primar
- Only those records from [Is a parent?] which equal #N/A.
Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.
Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.
Cheers,
Alex
I have a table with RandomID, EmpID, CompanyID. Some Companies will have great than 25 employees. I need to setup a query where I can randomly select all employees from all companies whos employee count is less than 25.
I have tried the following:
SELECT top 10 percent Count(*), empID
FROM tlbCompany
Group By empID
HAVING Count(*)<25
ORDER BY rnd(RandomID);
This counts the total number of employees, so if a company has 10 employees, it will only display one of the them.
Can this query be done?
And, what am I doing wrong?
Thanks to all that help....
Enviva..
Let me just start out by saying that when it comes to Access, I ride on the "little bus", so I apologize in adavance if this is a lame question.
I have a table with two date/time fields. One for reporting the date and time of an issue start, the other for the date and time of an issue ending. I'm trying to find a way to get the number of hours between the two without including weekends. I found a module on mvps.org that excludes workdays. However it doesn't seem to work in this case. For instance, if I put in an issue start as 02/10/2006 7:30 am, and the issue end as 02/13/2006 6:45 am, it only counts it as one workday. It won't count the 13th as a workday until the time is >= 7:30 am.:confused:
I'm simply stumped. Any help would be greatly appreciated.
Thank you,:)