Query question 12-11-07

I have a question that I hope you all can help me with. When I run a query, 
the results come back with multiple lines of data for an order because of 
multiple critereas in another field. Here is an example of the data returned 
to my query:

Order       Status
A             In Process
A             Sent
B             In Process
C             In Process

I would like to see only the data for orders that have not been shipped, and 
totally exclude data for orders that have been sent. In the example above, I 
would like my query results to show Orders B and C, but no data for A since 
it has already been Sent. I am relatively new to Access and have no idea how 
to create a formula for this. 

Thanks for your help and I hope this makes sense
0
Utf
12/11/2007 9:39:00 PM
access 16762 articles. 3 followers. Follow

2 Replies
752 Views

Similar Articles

[PageSpeed] 24

You're best off creating a subquery of all of those that have been sent, and 
exclude those orders in the main query:

SELECT Order, Status
FROM MyTable
WHERE Order NOT IN (SELECT Order
FROM MyTable
WHERE Status = "Sent")

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Pj" <Pj@discussions.microsoft.com> wrote in message 
news:B95517CE-56A7-48DF-A10A-E395F30957BA@microsoft.com...
>I have a question that I hope you all can help me with. When I run a query,
> the results come back with multiple lines of data for an order because of
> multiple critereas in another field. Here is an example of the data 
> returned
> to my query:
>
> Order       Status
> A             In Process
> A             Sent
> B             In Process
> C             In Process
>
> I would like to see only the data for orders that have not been shipped, 
> and
> totally exclude data for orders that have been sent. In the example above, 
> I
> would like my query results to show Orders B and C, but no data for A 
> since
> it has already been Sent. I am relatively new to Access and have no idea 
> how
> to create a formula for this.
>
> Thanks for your help and I hope this makes sense 


0
Douglas
12/11/2007 9:49:56 PM

"Pj" <Pj@discussions.microsoft.com> escreveu na notícia da 
mensagem:B95517CE-56A7-48DF-A10A-E395F30957BA@microsoft.com...
> I have a question that I hope you all can help me with. When I run a 
> query,
> the results come back with multiple lines of data for an order because of
> multiple critereas in another field. Here is an example of the data 
> returned
> to my query:
>
> Order       Status
> A             In Process
> A             Sent
> B             In Process
> C             In Process
>
> I would like to see only the data for orders that have not been shipped, 
> and
> totally exclude data for orders that have been sent. In the example above, 
> I
> would like my query results to show Orders B and C, but no data for A 
> since
> it has already been Sent. I am relatively new to Access and have no idea 
> how
> to create a formula for this.
>
> Thanks for your help and I hope this makes sense 

0
rozane
12/12/2007 1:14:09 AM
Reply:

Similar Artilces:

Format Cells #11
In one particular worksheet If I right click on a cell and click on "Format Cells..." the format cell window does not show up. The worksheet is not protected and there is no conditional formatting. If I create a new worksheet, rt. click on a cell and select format cell.. the popup works fine. what is a-miss with this spread sheet? ...

Field type question
Can you use a check box instead of a radio button? ...

CRM .NET Compatibility Question
So far you guys have been a tremendous assistance. I just need one clarification if anyone knows. Is the CRM incompatible with .Net Framework 1.1 or just Windows 2003 implimentation of .Net Framework 1.1. I have built a Windows 2000 server and am ready to intall CRM but am not sure if I should use the .Net Framework version 1.1 which is on the windows update site or do I need to dig around for version 1.0 Thank you much River Cox USArchive and Imaging Services. version 1.0 it is Has anyone tried with .net 1.0 sp1 We are currently using sp2 (which I believe CRM recommends anyways). &qu...

Advanced Filter...Load from Query...Help!
I have a report that I am trying to filter. I have created a filter unter the Advanced...Advanced Filtor/Sort option (in Access 2007). I then Saved the Query, but later after the Report has been closed out, I have tried Advanced...Advanced Filter/Sort... Load from Query, but the selection list is always blank. The query is saved and I can execute it, etc... but I cannot get it to be loaded as the filter source. Am I doing something wrong or is this an Access bug? Thanks! ...

query design help
I have a small table in A2007 that has only a few fields in it. Date, Location, Accepted, Count (these are the ones that matter) I need to do a query that can group by Location and count the Yes/No's in the Accepted field. On any one date there might be 20 input from the same location, so I suppose the correct option is to group by location first. The end goal here is to create a report that groups by location code and counts the yes/no for that location. Currently I have them entering a date, but I want to get to a report that does the same thing Year to date, and mon...

Web query timeout set.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Conditional Formatting 11-18-07
I have set up a couple of field (dates) that when they meet certian criteria they change colour, ie; they change to orange a year before they run out and then red six months before they run out, is there any way you can get them to print out that way on a report -- Jessybloke in the UK Have you tried the same method in your report that you used in your form? This should be possible either using conditional formatting, or using some VBA code. How did you accomplish this task on your form? Perhaps this article might be helpful: Conditional Formatting http://www.access.qbuilt.com...

Can I exclude the criteria Value from the query results?
I am using the [Dupe_Joiner_Title_ID] to match a list of records from a duplicates table against an ID form field, but want to exclude the actual value that the query uses as the criteria from being seen in the continous form that is linked to the query, but shows all other matching records for the form's current record. This is the criteria code: [Forms]![frm_Runs]![frm_Street_Joiner_Main].[Form]![frm_Street_Joiner_Sub].[Form]![Joiner_Title_ID] that is in the 'Dupe_Joiner_Title_ID: Joiner_Title_ID' column. This is my SQL: SELECT Tbl_Street_Joiner_Dupes.Address, Tbl_Stree...

Please help on RMS Questions
Hello, Request you to please help me on following questions. 1) In RMS can we setup the Customerwise--Item Wise price ? 2) What is the impact if we hold the transaction more than day and closed the registered ? 3) If I did the transaction on Register A and hold I assume that we can Recall that Transaction any store specific register ? please correct me if I am wrong. 4) Is it possible to view the other store on hand Qty ? Your prompt reply will be highly appreciable. Thanks Sandip I will try to answer to my best understnding of what you wish to achieve: 1) In RMS can we setup the Cust...

I need a xtab query to show zero months.
I have Access2003 I want to show sales of products per month in a xtab query, but I need the non-sale months to be shown as a zero too. Please help, Frank Add an IN statement in your PIVIOT statement like this --- PIVOT Format([YourDateField],"mmm") In ("Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec"); -- KARL DEWEY Build a little - Test a little "Frank Martin" wrote: > I have Access2003 > > I want to show ...

Printing business cards #11
I've created my business cards in Pub. 2002 and print preview shows everything perfectly. However, when I print each row seems to print a little too hgih into the next until finally the last row of cards has a .25 inch gap at the bottom. Seemed to me it might be a feed problem with my printer, but I printed a test page and it was not skewed. I even tried adjusting ( in Pg Setup) the vertical gap between the rows and that moves the printing too much. Hope my rambles make sense. I've been messing w/ this off and on for 3 days now. -- Thank you, Christine It does sound like ...

AutoFitMergedCellRowHeight limitation 11-24-09
I developed a macro similar to the AutoFitMergedCellRowHeight code I found on this Forum. But operate on a single row, but both seem to suffer from the same limitation: It appears that AutoFit Row Height only adjusts a Row height up to about 11 or 12 standard row heights. When the amount of text in a merged cell requires the cell to be resized to a height larger than 11 or 12 standard rows high, the built-in resize function seems to be unable to resize the text so that it is all visible. This seems to be true for the mergedcell macro as well as for single cells with the W...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

Subform 03-17-07
I have a subform that will not show all records that are associated with the main form.At this time I have to go back to a "backup" to reinstall the database and then re-enter the lost records.I have 900 records in the mainform and 1600 records in the subform database....

Outlook 07 Outbox send issue
Folks, I have a weird situation. I am running outlook 2007 on Vista SP2 with Bitdefender as my anti virus. I have no issues receiving email and can successfully reply or forward emails as needed. However, any *Newly* created email refuses to leave my inbox and is accompanied by a the following in the Outlook Send/Receive Progress dialogue box: "'rosuud@charter.net-sending' reported error (ox800ccc13):'Cannot connect to the network. Verify your network connection or modem" I have taken my firewall and antivirus out of the equation, deleted and recreated Ma...

Junk Email #12
Is there a way to turn off Outlook's junk email filtering? My third party spam filter has more/better options, but the Outlook filter grabs most of the spam messages before my third party filter gets it, thereby negating the advantages of my third-party filter. -- John Kilgo JohnKilgo <john.kilgo.span@dotnetjohn.com> wrote: > Is there a way to turn off Outlook's junk email filtering? My third > party spam filter has more/better options, but the Outlook filter > grabs most of the spam messages before my third party filter gets it, > thereby negating the advanta...

Query to find the sum of fields...
Hello all,I have an orders table that stores all the orders from variouscustomers sorted by order date descending (I guess that part doesn'treally make a difference). Anyhow, I want a query that gives me a"grand total" of every order from a particular customer. I'm reallynot sure how to go about it... with a Select Query or CrossTab Queryor even a different way completely.What I have is a table called [ORDERS] with the following fields:OrderIDOrderDateCustomerIDOrderTotaletc.Obviously there are orders placed from the same customer throughoutthe year but would have different...

read receipt 12-02-09
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop Hi, <br> Can you tell me how to add a read receipt onto emails please? <br> thanks <br> john Hi John, see here for more details: <http://www.entourage.mvps.org/faq_topic/return_receipt.html> On 02/12/09 11:33, in article 59badf51.-1@webcrossing.JaKIaxP2ac0, "free@last@officeformac.com" <free@last@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email > Client: pop Hi, > Can you...

Outlook Locks Up #11
I have a reoccurring appointmenrt set that has 1 or 2 modified dates. When I try to access that appointment, Outlook totally locks up. I have tried rebuilding the .pst file and "detect and Repair". No help. I have found that it is usually in a recurring appointment that has gone bad during the week of the freeze (when did the freeze start?) 1) Open the Outlook but do not try to open the calendar. 2) Create a New folder (Calendar Test) 3) Do an advanced find on the old calendar and sort events by recurrence. - Press F3 - Change the Look For: text box to Appointment...

Calculate MTD and YTD in a query
I need to calculate sales order MTD totals and YTD totals per month in a query. The query will show the following fields: Part#, Order Period (YYYYMM format), MTD Orders, YTD Orders I am getting the information from our transaction-based information system which stores all orders by date. I will need to group the orders by Part# and period, then sum them by month and by year-to- date. I need to see this information for the current month and year as well as for all previous months and years. It's easy enough to run totals per month, but the YTD is another story. Please help! Thanks Per...

Running parameter queries from VBA, suppressing parameter prompts
Hi everyone, I would like assistance with the following: I have a parameter query: PARAMETERS dt1 DateTime, dt2 DateTime; SELECT Table1.name, Table1.dt, IIf(Month([dt])=Month([dt1]),[number],0) AS mth0, IIf(Month([dt])=Month([dt1])+1,[number],0) AS mth1, IIf(Month([dt])=Month([dt1])+2,[number],0) AS mth2 FROM Table1 WHERE (((Table1.dt) Between [dt1] And [dt2])); Where dt1 = Start Date and dt2 = End Date This query is set up to run some moving 3 monthly data. I have a form to collect the users and I have the following VBA code: SomeButton_Click() On Error Resume Next...

Date query 01-21-10
I have a table which contains a field called "DUEDATE", data type is DATE/TIME, formatted as LONGDATE. I use it to enter in dates when projects are due. How can I write a query which returns "DUEDATE" items for the current date. Example: Today is January 21, 2010 and I want to retrieve everything from the table with "DUEDATE" of january 21,2010. If you only store the date part and only want to return records where the DueDate is the current date, set the criteria under DueDate to: =Date() Duane Hookom MS Access MVP "Iago" <Iago@d...

AutoFilter in Query not pulling blank cell
Hello, I have a query with this in the criteria: Like "*" & [Forms]![frmForm1]![cboNames] & "*" and in the cboNames I have : me.requery the autofilter cboNames works. But when I first open the form, cboNames is still blank, the form only query whose records containing something in the Names. please help, is there a way to pull everything, even blank records? I even tried: Like "*" & [Forms]![frmForm1]![cboNames] & "*" Or Is Null. but it messes everything up. I have several of these autofilter on my form. Switch to query to...

Footer questions
Using XL 2000 How can I (1) reference a custom footer to a cell in the speadsheet? I would like to have in Left Footer - "prepared on" and in the center footer a date which is entered in a cell in the spreadsheet. AND (2) if you create a custom footer with font formatting in one sheet of a workbook, how can you have it placed on the footer for every sheet in the workbook? many thanks Doug Hi 1. Only possible with VBA using an event procedure Before_Print 2. Select the first sheet with this footer and group all other sheets (hold down the cTRL keya while selecting the other tab...

lsass.exe error 11-28-09
Whenever i turn on my computer and get to the logon screen, i get a message saying: " System shutdown The system is shutting dowm. Please save all work and log off. The shutdown wa s initiated by \ Time before shutdown: 00:00:59 [this counts down to 0] -Message- The system process 'C\WINDOWS\system32\lsass.exe' terminated unexpectedly with status code 1073741819. This system will shutdown and restart. " When i press control-alt-del and type in my username to log in, it just comes up with "System could not log you in due to the following e...