query

R

Ruth

Hi there

I am setting up a query and want the criteria that if "WWI" is in one or
more of the columns (6 to choose from) then the entry shows up. How is this
done?

In the query design view, I tried putting "WWI" in the 'or' row, but none of
the entries show up.
 
J

John W. Vinson

Hi there

I am setting up a query and want the criteria that if "WWI" is in one or
more of the columns (6 to choose from) then the entry shows up. How is this
done?

In the query design view, I tried putting "WWI" in the 'or' row, but none of
the entries show up.
Click to expand...

You need it in all six columns, on different rows. If there are any *other*
criteria, they also must be repeated on all six rows. Or you could use SQL
view with a criterion like

WHERE ... <some criteria> ... AND ([Field1] = "WWI" OR [Field2] = "WWI" OR
[Field3] = "WWI" OR [Field4] = "WWI" OR [Field5] = "WWI" OR [Field6] = "WWI")

Note the parentheses! they're required if you have any other criteria.

If you have six fields that you need to check for a value, your table design
may need to be reconsidered! What are these six fields?


John W. Vinson [MVP]
 
R

Ruth

Thank-you!

The 6 fields are load and unload ports. I any given trip there could be 3
load or unload ports so I have all as seperate fields. I need a query to
show if a vessel went to WWI at any point in a trip.


--
Thank-you!
Ruth


John W. Vinson said:
Hi there

I am setting up a query and want the criteria that if "WWI" is in one or
more of the columns (6 to choose from) then the entry shows up. How is this
done?

In the query design view, I tried putting "WWI" in the 'or' row, but none of
the entries show up.
Click to expand...

You need it in all six columns, on different rows. If there are any *other*
criteria, they also must be repeated on all six rows. Or you could use SQL
view with a criterion like

WHERE ... <some criteria> ... AND ([Field1] = "WWI" OR [Field2] = "WWI" OR
[Field3] = "WWI" OR [Field4] = "WWI" OR [Field5] = "WWI" OR [Field6] = "WWI")

Note the parentheses! they're required if you have any other criteria.

If you have six fields that you need to check for a value, your table design
may need to be reconsidered! What are these six fields?


John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

Thank-you!

The 6 fields are load and unload ports. I any given trip there could be 3
load or unload ports so I have all as seperate fields. I need a query to
show if a vessel went to WWI at any point in a trip.
Click to expand...

Someday there may be four. Then what do you do? Redesign your table, all your
queries, all your forms, all your reports? Ouch!

If you have a One (trip) to Many (ports) relationship, the proper structure is
to have two tables, trips related one to many to PortCalls.


John W. Vinson [MVP]
 
R

Ruth

But would I have to create subforms to enter the data?
It makes it very confusing for the input of data, unless there is a way to
redesign the subform so it is in a easier format to use.
 
J

John W. Vinson

But would I have to create subforms to enter the data?
It makes it very confusing for the input of data, unless there is a way to
redesign the subform so it is in a easier format to use.
Click to expand...

What do you find confusing? A Continuous subform with one row per port (or per
loading/unloading step, or whatever makes sense in your application) doesn't
sound any more confusing than four textboxes to me. What do you see, and what
would you like to see?

If you're assuming that a subform must be in the (default) datasheet view...
it needn't.

John W. Vinson [MVP]
 
R

Ruth

Yes, I am assuming that the subform has to be in the datasheet view. I have
tried to change it before and can not figure it out. How do you change the
layout of it? Can the layout it be custom designed? How is it done?
 
J

John W. Vinson

Yes, I am assuming that the subform has to be in the datasheet view. I have
tried to change it before and can not figure it out. How do you change the
layout of it? Can the layout it be custom designed? How is it done?
Click to expand...

Simply open the Form you're using as a subform; view its Properties; and
change the Default View property from Datasheet to Continuous.

Arrange the controls on the form so they're all jammed up to the top of the
detail section, side by side - in two rows if you have a lot of controls.
Change textboxes to combo boxes as appropriate. Then drag the bottom of the
detail section up to the bottom of the controls and save the form.

When you open it as a subform you'll see as many rows of controls as there are
child records, with a blank row at the bottom for entering new ones.

See the Orders form in the Northwind sample database for a working example.

John W. Vinson [MVP]
 
Top