By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a


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

P: n/a

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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.