Delete Queries using Unmatched Query

Reading many helpful responses on this site, but unfortunately none of them 
seem to remedy my current issue.

Just learning Access, took a quick college prep course, got the basics.  Now 
I am expanding freelance.  SO the scenarios is my test idea that I have 
intent to apply on larger scale.  I have a database with 15 records... lets 
say reservations (tbl_reservations) for a hotel.  Used a maketable to copy 
that table because it gets updated often to cancelled or complete status.  So 
I filter out the 9 of those 15 reservations which are still in pending status 
and make the new table (tbl_pending_reservation) from those.  The next day I 
append 4 more pending reservations from the reservations table and add then 
to pending res table.  but now 4 of the original 9 reservations are 
cancelled.  I perform an unmatched query to find the 4 listed as pending in 
the pending reservation and listed as cancelled in the reservations table.

What is the best method of deleting the 4 records that are incorrect on the 
pending reservation table.  (keep in mind this is 4 records, but in the end 
it may be 1000's of records.)

I appreciate anyone taking the time to respond as this may be a wonderful 
example for others.
0
Utf
8/3/2007 6:42:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1129 Views

Similar Articles

[PageSpeed] 1

I will take your suggestion and apply it in the manner you suggest, but in 
practice, is there a way that a match query or unmatch query can be used in a 
delete query?

"Dale Fye" wrote:

> Not sure why you need a "Pending Reservations" table; is this as opposed to 
> "Confirmed Reservations"?  It is not usually a good idea to store the same 
> information in multiple tables for just this reason.  When you update one, 
> you have to update the other.
> 
> I think I'd just have a Reservations table.  Any reservation that starts on 
> a future date, and does not have a confirmation number is a pending 
> reservation.  Then, when you cancel the reservation, you only have to do it 
> in one place.
> 
> If you are stuck with this structure, I would set it up so that as soon as a 
> reservation is cancelled, it is deleted from the Pending Reservations table.
> 
> HTH
> Dale
> -- 
> Email address is not valid.
> Please reply to newsgroup only.
> 
> 
> "cmiller" wrote:
> 
> > In additional to the given scenario, also please assist me with inverse of 
> > scenario.  Let us assume after appending the pending reservations table to 9 
> > entries, and the append query was run again.  For whatever reason, all 9 
> > entries have been added a second time.  Running a Find Duplicates Query 
> > reveals all 9 records twice each.
> > 
> > How would I delete from the pending reservations, 1 of each entry?
> > 
> > "cmiller" wrote:
> > 
> > > Reading many helpful responses on this site, but unfortunately none of them 
> > > seem to remedy my current issue.
> > > 
> > > Just learning Access, took a quick college prep course, got the basics.  Now 
> > > I am expanding freelance.  SO the scenarios is my test idea that I have 
> > > intent to apply on larger scale.  I have a database with 15 records... lets 
> > > say reservations (tbl_reservations) for a hotel.  Used a maketable to copy 
> > > that table because it gets updated often to cancelled or complete status.  So 
> > > I filter out the 9 of those 15 reservations which are still in pending status 
> > > and make the new table (tbl_pending_reservation) from those.  The next day I 
> > > append 4 more pending reservations from the reservations table and add then 
> > > to pending res table.  but now 4 of the original 9 reservations are 
> > > cancelled.  I perform an unmatched query to find the 4 listed as pending in 
> > > the pending reservation and listed as cancelled in the reservations table.
> > > 
> > > What is the best method of deleting the 4 records that are incorrect on the 
> > > pending reservation table.  (keep in mind this is 4 records, but in the end 
> > > it may be 1000's of records.)
> > > 
> > > I appreciate anyone taking the time to respond as this may be a wonderful 
> > > example for others.
0
Utf
8/3/2007 8:12:00 PM
Reply:

Similar Artilces:

Excel query from Access databases
Hi, I have two separate databases in Access that have a common field of CustNo. I'll like to run a query in Excel using Get External Data... whereby I could create a report that brings in data from both the databases. Having tried this in Excel, it seems that I can only use one table at a time, is there a way to overcome the issue. Thanks, Rob Hi Rob- "I have two separate databases in Access that have a common field of CustNo." Is that accurate or do you mean 2 separate Tables in 1 Access DB file which use CustNo as the common field? AFAIK you can only query one Access...

Postal Bar Coding & Using Outlook Address Book in Publisher
I would like to add bar coding to my envelopes but can't seem to find any help in Publisher for this feature. I'd also like to find a way to use my Outlook Address book in Publisher - can anyone help me. Thanks After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Sharon Fermer <anonymous@discussions.microsoft.com>... > I would like to add bar coding to my envelopes but can't seem to find > any help in Publisher for this feature. Read www.mvps.org/the_nerd/Publisher/FAQs.htm > I'd also like to find a way > to use my Outlook Address bo...

Query most recent records only
I am not having much luck with trying to write a query to find the last (most recent dates) 5 records for ALL names listed in the 'Customers' table. SELECT tblOrders.*, tblCustomers.ID FROM tblOrders RIGHT JOIN tblCustomers ON tblOrders.Name = tblCustomers.Name ORDER BY tblCustomers.ID, tblOrders.Date; Appreciate any help, Kevin -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1 hi Kevin, On 05.03.2010 05:23, KevinE via AccessMonster.com wrote: > I am not having much luck with trying to write a query t...

#VALUE! when using defined name
HELP!!! Problem: receive #VALUE! Using a defined NAME range In workbook MonthlyBudget09Mstr.xlsx are sheets 1209Sch and 1209 1209Sch contains all monthly entries, 1209 is the summary sheet. Defined name: HousingItems, ='1209Sch'!$L$83:$L$88, Values: {"1";"2";"3";"4";"5";"15"} Workbook <<from Name Manager L88 is a SUM function but I have remove L88 from range and still have problem Placing =HousingItems in cell B4 of sheet 1209 results in #VALUE! Error B4:B9 are empty Can copy and then paste 1209Sch L8...

Will un- & reinstalling Outlook'03 delete email & addresses?
I've been plagued with an Outlook problem for about a month. Settings are right. Test email works fine. All other internet related programs are fine. But Outlook gives me a "send/receive error" message as fast as I can hit "Send/Receive". If I keep it open just to check contacts or calendar it always crashes within 10 to 60 minutes. If I start it in safe mode it seems to work all right. I reinstalled and it worked for 30 minutes and then did the same thing. If I Uninstall first, and then Reinstall, will it help? Will I lose my contacts and emails? Help! Find...

Outlook 2003 won't let me delete msg in Outbox
I have an email in my outbox that has a 6 MB attachment. Well, for whatever reason, Outlook was having issues sending the email, so after several hours, I decided to delete it. Pressing the delete button does nothing, no error. Trying to permenantly delete it (shift delete) and it gives me the error message "The item could not be deleted. It was either moved or already deleted, or access was denied." It's a bizarre error. I'm only using Outlook as a pop3 email reader. There's no exchange on the backend. This is with Windows XP Professional. Any help woul...

Querying MS Access from C# on stored file paths
Hi folks, I'm storing some particulars about file usage in my MS Access database, incl. the full paths of those files. I tried to query the database in my C# code. My idea was to retrieve from the database by means of SQL when a file was used the last time, but I only know the directory it is in, so in my code I tried to construct a WHERE clause as follows: String whereClause = "WHERE Filename LIKE '" + dirpath + "*'"; Of course I'm working on Windows and I don't want to think of portability now. Obviously, the variable dirpath contains double...

New mail appearing as old deleted item
When new mail arrives in inbox an old long deleted item appears in place of the new mail showing in bold as new mail. The real new mail is not visible at all and is therefore lost. This problem also used to happen in Windows Mail so I bought Outlook 2007 but it still happens intermittantly. -- Kate What do you see in your Unread Mail folder? "Kate B" <KateB@discussions.microsoft.com> wrote in message news:D9A95DD7-CFC4-423F-9B2B-F8B72177E2CE@microsoft.com... > When new mail arrives in inbox an old long deleted item appears in place of > the new mail...

Using Powerpoint 2007
PPS files on webpage open as PPT using PowerPoint 2007. Don't have this same issue when opening the same link from an Office 2003 computer, they run the show only. -- Miroshak ...

Use of Option Buttons
I have a main form where I display a record and associated fields. One of the fields is called 'source'. Currently, the source is linked to a one-to-one source table. No problem with this setup. However, I want to be able to assign and display multiple 'sources' for each main record. This ends up being a many-to-many relationship and, therefore, I have created the following tables (not all fields are listed). Program Table Prog_ID Program_Name Link Table Prog_ID Source_ID Source Table Source_ID Source_Name I want to display on the main form 'buttons' indicating ...

WHY DID YOU DELETE MY POST?
All I did was ask for some help with massive amounts of spam coming to my exchange server. Please explalin to me why you deleted my post I really need some help with this issue. On Thu, 9 Jun 2005 12:21:03 -0700, "dath" <dath@discussions.microsoft.com> wrote: >All I did was ask for some help with massive amounts of spam coming to my >exchange server. Please explalin to me why you deleted my post I really need >some help with this issue. You want to look again, Seth and Will replied. Seth said: depending on your hardware, etc you can do this on the same server, ...

How to use OpenArgs
Hi all, a pop-up form is opened from a subform with this code: Private Sub Form_Open(Cancel As Integer) Dim strCustomerName As String Dim strBuildingNo As Long If IsNull(OpenArgs) = False Then Dim arrArgs() As String arrArgs = Split(Me.OpenArgs, "~") strCustomerName = arrArgs(0) strBuildingNo = arrArgs(1) End If End Sub It is working good and my array, arrArgs, is populating strCustomerName and strBuildingNo perfectly. If a new record is needed I use the following: Private Sub Form_BeforeUpdate(Cancel As Integer) '...

Field lookup query question
Access 2003. I have two tables. Master and IssuePart. Each table has a report number (Master.OA) that ties all this together. I wanted to make the Master table perform a lookup by matching the report number in IssuePart and bringup a listbox. But the query won't cooperate. I can manually enter a report number and get the results I want, but as soon as I try to change the criteria to the Master report number field name, it chokes with syntaxs errors and such. Here's the SQL the Builder produces. SELECT IssueData.[Part Number], IssueData.[Serial Number] FROM IssueData WHERE ((ISS...

Using (1), (2), (3), etc. in Risks and Issues Category values?
Do most of you use numbers, like (1), (2), (3), to define Category values for Risks and Issues to keep it "the same" as the out-of-the- box values for Status? Just curious. Thanks Andy Novak UNT Andy, I have used both with numbers and without for projects, based on the clients maturity. I would suggest if you want to make it as easy as possible to differentiate between the different risk and issues categories, put the number in, it makes it much easier to see. Alex. On Feb 20, 8:48=A0am, "ano...@unt.edu" <ano...@unt.edu> wrote: > Do most of y...

Using eseutil.exe
What is the best way to determine, if my store need to be defragged with eseutil. We have been running W2K3 Exch for about 7 months, we have removed a lot of information from the store (by forcing users to clean up their sent items and deleted items folders) yet the store remains so large the backup will not fit on 1 40GB tape. Is this an indication that I should use the utility? If the online defragmentation runs as part of the online maintenance that should run every evening then you may be able to tell from this, have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;...

invalid use of property message
I am trying to cre4ate a macro that checks the value of a number of sheets to determine if they are already visible and if so it will not close them - With Sheets("Pay Inflation - Biometrics") Sheets ("Statistics") Sheets ("Direct Cost Savings Breakdown") Sheets ("OT Reduction") Sheets ("Nurse OT Reduction") Sheets ("Premium Labor Utilization") Sheets ("Pay inflation - Timestamp") Sheets ("Calculation Error") Sheets ("Le...

Delete Administrators Mailbox
I have an administrators mailbox of 4.7GB - what repprcussions are there if I delete this ? would the option be to re-home the e-mail account first ?,then delete the msilbox -- William W Davidson Network Engineer On Tue, 7 Dec 2004 08:53:02 -0800, "William Davidson" <WilliamDavidson@discussions.microsoft.com> wrote: >I have an administrators mailbox of 4.7GB - what repprcussions are there if I >delete this ? > >would the option be to re-home the e-mail account first ?,then delete the >msilbox You want to find out what's in it first and then prevent i...

Deleting Records based on criterion
I know about advanced filters and filtering out records based on a criterion. But if I wanted to use the approach of deleting the records that don't match a criterion, how would I go about this in Excel? I would rather not use a macro for this unless its the only way. Thanks, Bruce After you do your Autofilter (or in-place Advanced Filter) only the rows to be deleted will be visible. Select these rows (you only need to select one column) and do an Edit, Delete Row. Click Ok to the confirming prompt. Only the visible rows will be deleted (Excel is pretty smart<g>) just as y...

how to use Field codes in Excel
Hi, I am facing a problem in using custom property values in excel worksheet. Here is my requirement. I want to display the Version No in the footer. how to do the same using custom properties. I have used field codes in Word but could not find the same in excel. Can you pls help ASAP "Radhika" wrote... >I am facing a problem in using custom property values in >excel worksheet. Here is my requirement. I want to >display the Version No in the footer. how to do the same >using custom properties. I have used field codes in Word >but could not find the s...

CHM Help using MFC and VC6
I'm not certain I'm in the right place, so please direct me if there is another forum where this question would be more appropriate. Two questions: 1) Migrating an existing application from using WinHelp to using HTML Help. 2) Building a new application to use HTML Help, using VS6 and MFC. Everything I've found in the MSDN so far is suggesting that building an application in VS6 to use HTML Help is a brutal and bloody process. The preferred approach seems to be use VS.Net. This doesn't make sense, since HTML Help existed long before VS.Net. I can't imagine ...

How to install software on a terminal server/use microsoft office through RDP
My boss is asking me to figure this out, so I do not know all the configurations he is using. I can tell you its Windows server 2k3. He wants to be able to RDP to server, and work physically on the server. He also needs to have office 2003 license installed. So the greater question is "how to use ms office 2k3 through RDP on microsoft 2003 server?" -- jgosney ------------------------------------------------------------------------ jgosney's Profile: http://forums.techarena.in/members/159717.htm View this thread: http://forums.techarena.in/windows-server-help/1277...

Imported/merged .ics file into calendar... how to undo/delete?
Hello. I accidentally imported an .ics file into my main calendar and am wondering if there's anyway to remove the entries that came from this ics file from my calendar? Thanks. Add the modified time field to the view then sort by it - all the imported events should be together with the same modified time. -- -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about...

Query Results Where Qty Shipped is not Divisable by 4
How would I construct a query to return all invoices where the qty shipped for a specific item is not divisable by 4? Really its the divisable by 4 part need assistance with. Thanks! SELECT * FROM YourTableName WHERE YourFieldName Mod 4 <> 0; -- Ken Snell http://www.accessmvp.com/KDSnell/ "meangene" <meangene@discussions.microsoft.com> wrote in message news:2684394D-463B-4804-970F-79205F41C039@microsoft.com... > How would I construct a query to return all invoices where the qty shipped > for a specific item is not divisable by 4? Rea...

How to read B-LOB data from Oracel DB using Pro*c
Hello, I'm trying to read B-LOB data in Oracle Database using Pro*c. I know how to read it with char mode. What I want to know is read it with binary mode. Are there any good person help me? Let me know if you have a good sample source. Thanks and have a nice day. ...

deleting a calendar series needs new option
When you have a series for a meeting appointment and after a year there is no longer a meeting your first impulse is to delete the meeting series from your calender. When you do this it deletes all past, present and future occurrances of the appointment. It would be really nice if the delete had an option to say from here forward so you can preserve history in the calender. Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in...