473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help writing a macro or query.. not sure which!

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

Nov 13 '05 #1
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;

Nov 13 '05 #2

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

Nov 13 '05 #3
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...

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
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...
0
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...
3
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...
8
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...
6
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...
53
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,...
3
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....
0
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...
1
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
0
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...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.