I'm trying to design and write a little module (or macro even) for
someone
in my office, but I'm having some trouble. I have the following
database
structure (Note: just some sample data, obviously):
SystemNumber SystemNumberMAX SystemName SystemKey
9007404 Windows Test 1 A
9007405 Windows Test 1 A
9007406 Windows Test 1 A
9008683 Windows Test 45 A
9008684 Windows Test 45 A
9017616 Windows Test 4595b A
9019528 Windows Test 1 B
The SystemNumberMAX field is to be populated with the maximum
SystemNumber
for a group, where a group is a unique System Key and System Name
combo. The
SystemNumberMAX is only to be filled when there is more than 1 System
Number
for a group. Thus, when the module is complete, the above record set
should
look like this:
SystemNumber SystemNumberMAX SystemName SystemKey
9007404 9007406 Windows Test 1 A
9008683 9008684 Windows Test 45 A
9017616 Windows Test 4595bg A
9019528 Windows Test 1 B
I'm totally stumped though! I've only done stuff like affecting forms
and
switchboards in Access/VBA. I'm thinking the steps are:
1. Select the max SystemNumber # for the group and write it to a
variable.
2. Delete the records in the range between the minimum and maximum
SystemNumber for the group (including the record for the max
SystemNumber)
3. Write the max SystemNumber into the SystemNumberMAX field.
Can anyone offer guidance or example code for something similar?
Thanks! -Mike 3 1405 ce*********@yahoo.com wrote: I'm trying to design and write a little module (or macro even) for someone in my office, but I'm having some trouble. I have the following database structure (Note: just some sample data, obviously):
SystemNumber SystemNumberMAX SystemName SystemKey 9007404 Windows Test 1 A 9007405 Windows Test 1 A 9007406 Windows Test 1 A 9008683 Windows Test 45 A 9008684 Windows Test 45 A 9017616 Windows Test 4595b A 9019528 Windows Test 1 B
The SystemNumberMAX field is to be populated with the maximum SystemNumber for a group, where a group is a unique System Key and System Name combo. The SystemNumberMAX is only to be filled when there is more than 1 System Number for a group. Thus, when the module is complete, the above record set should look like this: SystemNumber SystemNumberMAX SystemName SystemKey 9007404 9007406 Windows Test 1 A 9008683 9008684 Windows Test 45 A 9017616 Windows Test 4595bg A 9019528 Windows Test 1 B
I'm totally stumped though! I've only done stuff like affecting forms and switchboards in Access/VBA. I'm thinking the steps are:
1. Select the max SystemNumber # for the group and write it to a variable. 2. Delete the records in the range between the minimum and maximum SystemNumber for the group (including the record for the max SystemNumber) 3. Write the max SystemNumber into the SystemNumberMAX field.
Can anyone offer guidance or example code for something similar?
Thanks! -Mike
I'm not quite clear on exactly what you want in the first column of your
results. From looking at your sample result, I _think_ that you want the
minimum system number for the given (systemname, systemkey) combination
- is that right?
If that's what you're after, the following single query ought to do the
trick:
SELECT Min(systemnumber) AS Minsystemnumber,
IIf(Max([systemnumber])>Min([systemnumber]),Max([systemnumber]),Null) AS
Maxsystemnumber,
systemname,
systemkey
FROM yourtable
GROUP BY systemname, systemkey;
Helen Wheels wrote:
Thus, when the module is complete, the above record set should look like this: SystemNumber SystemNumberMAX SystemName SystemKey 9007404 9007406 Windows Test 1 A 9008683 9008684 Windows Test 45 A 9017616 Windows Test 4595bg A 9019528 Windows Test 1 B
I'm not quite clear on exactly what you want in the first column of
your results. From looking at your sample result, I _think_ that you want
the minimum system number for the given (systemname, systemkey)
combination - is that right?
If that's what you're after, the following single query ought to do
the trick:
SELECT Min(systemnumber) AS Minsystemnumber, IIf(Max([systemnumber])>Min([systemnumber]),Max([systemnumber]),Null)
AS Maxsystemnumber, systemname, systemkey FROM yourtable GROUP BY systemname, systemkey;
Wow, I was further away than I thought!! haha.. Anyway, yes, I want the
minimum system number in the first column, and the maximum in the
second column.
However, this seems to not quite be working for me... It is operating
as a parameter query for some reason. It is putting whatever I enter
in the parameter popup as the first column in the results (the Minimum
one) and nothing in the second (maximum).
I had to make some changes to field names to accommodate the actual
database structure, here is the exact query I am using in case you can
pick up any typos or the like:
SELECT Min([GroupNumber]) AS MinGroupNumber,
IIf(Max([GroupNumber])>Min([GroupNumber]),Max([GroupNumber]),Null) AS
MaxGroupNumber, GroupName, CustSvcInd
FROM ICISGroups
GROUP BY GroupName, CustSvcInd;
Thanks a lot! -Mike ce*********@yahoo.com wrote: Helen Wheels wrote: Thus, when the module is complete, the above record set
should look like this: SystemNumber SystemNumberMAX SystemName SystemKey 9007404 9007406 Windows Test 1 A 9008683 9008684 Windows Test 45 A 9017616 Windows Test 4595bg A 9019528 Windows Test 1 B
I'm not quite clear on exactly what you want in the first column of
your
results. From looking at your sample result, I _think_ that you want
the
minimum system number for the given (systemname, systemkey)
combination
- is that right?
If that's what you're after, the following single query ought to do
the
trick:
SELECT Min(systemnumber) AS Minsystemnumber, IIf(Max([systemnumber])>Min([systemnumber]),Max([systemnumber]),Null)
AS
Maxsystemnumber, systemname, systemkey FROM yourtable GROUP BY systemname, systemkey;
Wow, I was further away than I thought!! haha.. Anyway, yes, I want the minimum system number in the first column, and the maximum in the second column.
However, this seems to not quite be working for me... It is operating as a parameter query for some reason. It is putting whatever I enter in the parameter popup as the first column in the results (the Minimum one) and nothing in the second (maximum).
I had to make some changes to field names to accommodate the actual database structure, here is the exact query I am using in case you can pick up any typos or the like:
SELECT Min([GroupNumber]) AS MinGroupNumber, IIf(Max([GroupNumber])>Min([GroupNumber]),Max([GroupNumber]),Null) AS MaxGroupNumber, GroupName, CustSvcInd FROM ICISGroups GROUP BY GroupName, CustSvcInd;
Thanks a lot! -Mike
Hi Mike
I can't see any obvious errors in the above, but of course I don't know
what your actual table and field names are. If "GroupNumber" was
mistyped in the SQL (e.g. if the field in your table is really called
"GroupNo"), the query's behaviour would be exactly as you describe.
So, could you check that the field names aren't mistyped? Sorry to ask
the obvious, but it's usually the best place to start... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jeremy Weiss |
last post by:
I'm trying to build a database that will handle the monthly billing needs of
a small company. I'm charting everything out and here's what I see:
table for customers
sub table to track payments...
|
by: Colin |
last post by:
In access 2000 I need to filter records in a Subform by pushing a
button on a command button located on the Main form.
The Main form is blank. Its only purpose is to contain the subform
which is...
|
by: radioman |
last post by:
Hi all, I would appreciate some help please.
I just need pointing in the right direction as I am at a loss.
Basically I have a form (frmAddMaster) which displays two subforms
"Master Stock...
|
by: Steph |
last post by:
Hi.
I'm very new to MS Access and have been presented with an Access database
of contacts by my employer.
I am trying to redesign the main form of the database so that a button
entitled...
|
by: Takeadoe |
last post by:
Dear NG,
Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the...
|
by: Hexman |
last post by:
Hello All,
I'd like your comments on the code below. The sub does exactly what I want it to do but I don't feel that it is solid as all. It seems like I'm
using some VB6 code, .Net2003 code,...
|
by: Bidhan |
last post by:
Hi,
I have a table (Stock) important field are partsNo., des, qty, pPrice, sPrice, qtyBuy. I make a query based on the table and has one more field SAB(qty+qtyBuy). I made a form based on the query....
|
by: gunimpi |
last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431
********************************************************
VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help
wanted...
|
by: rfranzl |
last post by:
Hello,
I need some help, I have about 200 databases that are copies of an original database that has a similiar table in all of the databases, called "tblCodebook".
What I am trying to do is to...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |