Criteria, queries, expressions

[marthacasting]

First let me say that I am a novice Access-user.

Let's say that I want to make a report to use as a packing list for equipment to be sent to different camps, based on information we get on each camper.
We ask the campers for their shoe size and their pant size. The information is automatically imported from an online application. So I have fields in my query for ShoeSize and PantSize.
The campers don't know that we make adjustments/accommodations to the equipment based on the information they give us.
If their pant size is S,M,L, then we move up their shoe size by one size. If their pant size is XL, XXL, XXXL, then we move up their shoe size by two sizes.
So I would like to set up the query (to generate reports for the equipment packer) so that it makes this adjustment automatically in another field.
My question is, can I have a ShipShoeSize field set up based so that ShipShoeSize: is based on PantSize+1 when the PantSize is S,M,L and PantSize+2 when the PantSize is XL, XXL?
I was thinking in the ShipShoeSize criteria, I could go into Builder and have it be Pants=S or M or L, but I haven't been able to get it to work.

Thank you in advance for any help!

 

[plog]

Let's look at this mathematically first and reduce the problem: You are always adding 1 to the shoe size. If the pant size starts with an 'X' you are adding an additional one to the shoe size. Therefore you only need to test to see if the pant size starts with an 'X'.

Actually, it even easier if you are mathematically doing this. Hopefully you have a table with sizes in them that looks something like this:

SizeNumber, SizeDescription
1, Small
2, Medium
3, Large
4, XL
...

That way you can truly do math on the sizes (SizeNumber +1), otherwise you are going to need something to do the logic for you so that you know M is before L, but after S.

Then you can use a simple Iif statement (http://www.techonthenet.com/access/functions/advanced/iif.php) to add one to the shoe sizes when the pant size is greater than 4 (see above for how we converted sizes to numbers).

 

[spikepl]

For a mere $1000 paid within 24 hours I shall refrain from revealing your skulduggery to the campers!

For another $1000 I'll also tell you which labels were switched on which pants.

 

[marthacasting]

Hi there,

Thank you for your response! I'm sorry to say that it does not make sense to me.

If the pant size is S,M,L, XL (I goofed on that before), then I want to add 1 size to the shoe size.
If the pant is XXL or XXXL, I want to add 2 sizes to the shoe size.

I had set up two new fields-- ShipShoe: [ShoeSize] + 1 and ShipShoe: [ShoeSize] + 2
Then I was wondering if in the first Criteria field, I could have Pant size = S or M or L or XL and in the second Criteria field have PantSize = XXL or XXXL, but I can't seem to set that up so it works.
I do also have a field of Camps (for example, NW, North, East, SE, South), so that I can create my query based on which camp/campers I need to get the equipment for. (So in the Criteria part of the Camps field, I already have the Criteria North, to separate this group out.) Can I have Criteria set in more than one field in a query?

Again, my thanks for any help in simplifying this!

 

[plog]

What doesn't make sense?

I had set up two new fields-- ShipShoe: [ShoeSize] + 1 and ShipShoe: [ShoeSize] + 2

You can't create 2 fields with the same name in the same query. Which is moot, because you don't want 2 fields, you want 1 value for Ship Shoe but with 2 different ways to calculate it.

Then I was wondering if in the first Criteria field,

You don't use the criteria field for this. You use the Iif function to determine what the value of ShipShoe is.

Can I have Criteria set in more than one field in a query?

Yes, but it doesn't sound like you have 2 criteria, since you shouldn't be using the criteria area to determine ShoeSize.

Again, what part of my solution isn't making sense?

 

[marthacasting]

Sorry, this is all new to me. (Not that it is the way to go, but I did give 2 diff. names to the diff. fields.)
I've not used Builder before or the Iif function!

 

[ECEK]

I would create a new table with two columns.

size_rqd, increase_by
S,1
M,1
L,1
XL,2
XXL,2

Link this table to your initial table via the size_rqd to the column in your initial table.

In your query simple add the relevant columns together.

size_rqd:[initialtablesize]+[increase_by]

 

[marthacasting]

I just didn't understand

 

[JHB]

Post your database with some sample data, (zip it) + the name of the query/report you're trying to build.