Hey all,
I have a table of receipts used (Table Name: receipts, Field Name: receiptNumber) and a table with receipts issued (Table Name: Receipts Batches, Field Names: BatchID, BegRng, EndRng). Example row in Receipts Batches: 3, 72551, 72600. I need to check the list of used receipts against the issued list and find any "skipped" receipts; that is, find the max used receipt for each batch, and check if any below that number are missing. I've seen some loops and select statements out there, but couldn't come close to getting them to work! Thanks in advance for the help; this forums has so much good info!
20 3639
Hi. What an interesting problem! Here's a two-query solution. The first query finds all the receipt numbers that have been used in the batches, and the second uses this left-joined back to the receipts and selected for a null receipt ID to return all remaining receipt numbers not in the batch ranges:
qryAllUsedRecei pts - SELECT [Receipts Batches].BatchID, Receipts.ReceiptNumber, [Receipts Batches].BegRng, [Receipts Batches].EndRng
-
FROM Receipts, [Receipts Batches]
-
WHERE (((Receipts.ReceiptNumber) Between [BegRng] And [EndRng]));
-
qryReceiptNotUs ed -
-
SELECT Receipts.ReceiptNumber
-
FROM Receipts LEFT JOIN qryAllUsedReceipts ON Receipts.ReceiptNumber = qryAllUsedReceipts.ReceiptNumber
-
WHERE (((qryAllUsedReceipts.ReceiptNumber) Is Null));
-
I've tested these on a simple small dataset, where receipts just has the numbers 1 to 30 in sequence, and receipt batches is as follows: - BatchID BegRng EndRng
-
1 1 10
-
2 12 16
-
3 17 29
Note that the numbers 11 and 30 are not in the batch ranges.
qryReceiptNotUs ed returns
as expected. Try these out and see how it goes for you.
Regards
Stewart
NeoPa 32,579
Recognized Expert Moderator MVP
Without going into too much detail, I'd work on the basis that Count(*) == (Max() - Min() + 1).
From this point you can select all matching records or use some code to list the missing numbers. Clearly SQL would struggle to list those per se.
Hi. What an interesting problem! Here's a two-query solution. The first query finds all the receipt numbers that have been used in the batches, and the second uses this left-joined back to the receipts and selected for a null receipt ID to return all remaining receipt numbers not in the batch ranges:
qryAllUsedRecei pts - SELECT [Receipts Batches].BatchID, Receipts.ReceiptNumber, [Receipts Batches].BegRng, [Receipts Batches].EndRng
-
FROM Receipts, [Receipts Batches]
-
WHERE (((Receipts.ReceiptNumber) Between [BegRng] And [EndRng]));
-
qryReceiptNotUs ed -
-
SELECT Receipts.ReceiptNumber
-
FROM Receipts LEFT JOIN qryAllUsedReceipts ON Receipts.ReceiptNumber = qryAllUsedReceipts.ReceiptNumber
-
WHERE (((qryAllUsedReceipts.ReceiptNumber) Is Null));
-
I've tested these on a simple small dataset, where receipts just has the numbers 1 to 30 in sequence, and receipt batches is as follows: - BatchID BegRng EndRng
-
1 1 10
-
2 12 16
-
3 17 29
Note that the numbers 11 and 30 are not in the batch ranges.
qryReceiptNotUs ed returns
as expected. Try these out and see how it goes for you.
Regards
Stewart
Thanks Stewart. This worked, but it is not what I exactly what I was looking for. BUT it did reveal an issue that I didn't even see! It shows me if Receipts Used were not recorded as being Issued in a batch. So this query will be great.
For the original issue, I'll use your sample for an example. Let's say we keep the batches you have. Now, for receipts used, I have 1,2,3,4,6,7,12, 13,14,15,17,18, 19,20,23,24,25. What I want is to find the max used in the batch (1st batch = 7, 2nd batch = 15, 3rd batch = 25), and from there, check back to the beginning to see if any were not use (in this case, 5 for the 1st, none for the 2nd, and 21,22 for the 3rd). So the end result from the query would be Receipts missing = 5, 21, 22.
NeoPa 32,579
Recognized Expert Moderator MVP
As you'll see from my last post (#3), this cannot be done in SQL.
You will need to code this up in VBA.
A RecordSet loop is what you'll need. Start on that basis and let us know if/where you get stuck. Basic DAO recordset loop using two recordsets may help starting you going.
Thanks NeoPa. I feel fairly stupid to admit being stuck at the beginning. I think the example linked makes sense in what it is, but I don't see how to apply that to my situation. I apologize for the "newbieness "; nearly all my VBA has been in Excel, not Access.
NeoPa 32,579
Recognized Expert Moderator MVP
That's not a big problem, but as what you require is in the "very involved" range of coding solutions, I'm not about to do it all for you.
What I will try to do is a general pseudo-code layout of what is probably going to be required. From there you will be expected to build onwards (with help where required and appropriate). This is certainly no trivial question, so be prepared for some graft.
That's great. I don't want you to write it, for sure, that wouldn't be fair. I usually pick things up quickly, so some solid direction hopefully will be enough. Thanks!
NeoPa 32,579
Recognized Expert Moderator MVP
As a basic concept then, you would process through your dataset (The earlier link shows how to reference a dataset in VBA code).
You would have a variable that remembers the value from the previous record.
You would then have a loop of code that processes through for every record in the record set.
Compare the value in the current record with that of the previous (remebered) one.
If new = old + 1 then no extra action.
Otherwise you log the old remembered value as the last of a group.
Any other processing you want here.
Save the new "old" value in the variable.
Loop around until done.
I've been trying to figure this out for a while now. I don't understand how this will work though. I need to check each individual batch. How would I do that? And if the formula is just +1, what if the next one is 2 higher? I'm pretty stuck.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Dan Jones |
last post by:
I'm writing a script to process a directory tree of images.  In each
directory, I need to process each image and generate an HTML file listing
all of the images and links to the subdirectories.
Just about every source I can find on the 'net for processing subdirectories
points you at Find::Find.  However, I'm trying to do something like this:
enter directory
open INDEX, ".\index.html"
print INDEX HTMLheader
|
by: SQLJunkie |
last post by:
Here is an issue that has me stumped for the past few days. I have a
table called MerchTran. Among various columns, the relevant columns for
this issue are:
FileDate datetime
, SourceTable varchar(25)
SQL statement:
SELECT DISTINCT
FileDate
|
by: Justin Emlay |
last post by:
I'm hopping someone can help me out on a payroll project I need to
implement.
To start we are dealing with payroll periods. So we are dealing with an
exact 10 days (Monday - Friday, 2 weeks).
I have a dataset as follows (1 week to keep it short):
Employee 1 - Date 1
Employee 1 - Date 2
|
by: usenet |
last post by:
How and where can one find out about the basics of VB/Access2003
syntax?
I am a died in the wool C/C++/Java Linux/Unix programmer and I am
finding it difficult to understand the program format for accessing
objects, controls, etc. in VB/Access2003.
In particular where will I find explanations of:-
Actions, Functions, Methods, Properties - I'm understand the
|
by: Mahesh BS |
last post by:
Hello,
I need to write a query to find out a set of missing number in a given
sequence.
Eg : a Column in some table has the following data
| |
by: OppThumb |
last post by:
Hi,
I've been searching this newsgroup for an answer to my question, and
the closest I've come asks my question, but in reverse ("How to figure
out the program from plan/package"). I've -- shall we say, inherited?
-- a COBOL program with very little documentation that I've recompiled
for debugging purposes. The compile/link/bind have all been done, but
nothing in the output tells me what plan the program has been bound to,
so I can't...
|
by: danceli |
last post by:
I have made trigger on table 'FER' that would be fired if data is
inserted, updated to the table. And also, I made batch file using bcp
to extract the newly updated / inserted records.
But I got missing data in bcp out file like this:
Missing 1200 records, blocked at:
/*
777946 296188 2007-01-29 21:25:45.063
|
by: Ravi |
last post by:
Hi People,
I am facing an issue with printing where I have to print receipts of a
custom size. Each receipt is about 8 inch by 4inch. I am using
window.print(). When a receipt is printed another 3 receipts are
flushed out of the printer. I want only the printed receipt out of the
printer. Am using a dot-matrix printer and the receipts are in a
continuation (i.e around 1000 receipts attached to one another).
I want to print only one...
|
by: bwesenberg |
last post by:
I am not sure how to explain this so I will try to the best of my ability.
I need to be able to produce 1000 labels from access that are based on consecutive numbers that Access will produce.
Here are some of the criteria they are looking for:
Need to be able to produce labels on demand – generating at least 1,000 labels at a time.
The program will know what the last number used was so when they print the next batch of labels it...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |