20 field "or" query how do I do it

[Debbie Keller]

I have 20 names in 1 field of a table that I need to make an all "or" query on. The query genie only lets me do 10 or statements. With the query card I put the field name in and the 1st condition as =Carrizo 1, the I put all the rest in the "or" fields as =Carrizo 3 etc. When I go to the next line of the card I assume I put the field name in again and then I skip the condition field and start adding the rest of the "or" names. I know the names are in the table but the query always comes back as "no records found". I tried action coding as the example in the manuel and when it gets to the last line "tbl.query_create():" it won't compile and says that it is missing a symbol? I don't understand that statement.
I would really appreciate any help on the subject. I have faithfully tried to do this for several days and now my brain is in a muttle.

[Tom Cone Jr]

RE: 20 field

Debbie,

Have you tried query by form ?

You can use wildcards, and can separate alternative items with commas. Don't know if this will help, but it might.

Check the user's guide for query operators (or operators, query... I forget which).

You could put the following in a Lastname field query by form:

Carriz*, Smith, Jones*, Claxton

- tom

[Debbie Keller]

RE: 20 field

In Alpha4 I can write my query
code and this how I would write
it for the query that
I'm trying to do.
(Prj4name="Zone B".and. Prj4asnbr>0)
.or.(Prj4name="Zone D".and Prj4asnbr>0)
.or.((Prj3name="Elfrida 1.or.
Prj3name="Elgin 2".or.Prj3name="Eloy 2"
.or.(etc)).and.(Prj3asnbr>0)
This is basically how I would code
the query. I'm attaching a bitmap so
you can see how I'm trying to do it.
The only thing that actually works is
the first 2 lines. Can you help with
this?
Thank very much

[Tom Cone Jr]

RE: 20 field

Debbie,

the syntax should be the same.

Use Query by Table.

If still stuck post a zipped copy of the database here, or email it to me. In either case, you'll need to furnish a complete, detailed description of the desired filter.

-- tom

[Tom Cone Jr]

RE: 20 field

Debbie,

Each row in the Query by Table dialog represents an .AND. condition.

I think you need to build the expression from left to right on one row.

Generally, when one needs this many expressions to describe a particular data set, it's time to rethink the database design. Often it's far easier to include a simple flag field that gets set or lowered when the desired sequences are presented during data entry.

It's also easily possible to pre-process the query by running a series of update operations to set a flag field true, one condition at a time. At the end, you run a simple query to return just the fields where the flag has been set.

Lots of options to overcome the 1024 character limit on the overall length of a query expression.

-- tom

[Debbie Keller]

RE: 20 field

I think I need to give you a better explanation of what I'm trying to do.
1. I have a large set that has a hugh Parent table then it has 5, 1 to many child tables to the parent. Then the 5 child tables also have a 1 to many table attached to each of them.
2. The Names that I want in the query will not change and needs to be saved. That way we can send out cash demands for those particular Zones and not any others. There are probably 100 different Zones in the Database and there could be 1 zone in a search or 20 in the search because the zones are for installing waterlines and they bond the projects when they can. So each bond can have 1 zone or 15 zones or what ever.
3. I have the required search list in Alpha4 and it runs fine. I did code for the search list instead of using the table form that it has.
4. What I don't understand is that if I can do it in Alpha4 which is a much older program and can do much less than Alpha5v4.5 Where can I create it in Alpha5. I know I'm thick headed. If you still need to see the database it will take me a while to make it for you and send it. But, I will if you need it.
5. Thank you so much for your patience.

[Tom Cone Jr]

RE: 20 field

Debbie,

Alpha Four is a very mature product.

Alpha Five is evolving. One of the differences may be in cross-level queries. If you're working with the complex set you describe and you're using values in the child tables to find parent table records, you must use flattenquery(). Alternatively, you can restructure the set, inverting the original table relationships.

I suggest you start small.

Figure out how to do it with a simpler set containing one parent table, and one child, with one grandchild.

I would not need the entire data set, but would need a representative sampling, and many, many more details to assist. I'm afraid my volunteer time won't be up to it. Maybe others can assist ?

-- tom

[Tom Cone Jr]

RE: 20 field

Debbie,

I should hasten to add that cross-level queries are supported in Vers 5 of Alpha Five to a greater degree, than in Vers 4. However, I'm still not sure where your search condition fields are being pulled from. If they're fields in the parent table alone, then it's not a cross-level query in the first place, so ignore all that follows.

The terminology here is pretty slippery.

In Vers 4 of alpha five, its easily possible to frame a query against a 1:N set which will narrow the range of displayed children in ALL parent table records. However, all the parent table records will be returned. Only the children will be filtered, if you see what I mean.

It's not possible to frame a query against a set which will narrow the range of displayed parent table records, using filter criteria and search fields from the linked 1 to many child tables (unless you use flattenquery()). This is not the case with 1 to 1 links.

An easy solution is to design a new set, inverting the table relationships, so that they become 1 to 1, then the query can retrieve them all. Because of your multilevel set design I'm not sure this would be practical, but it might work.

--tom

[Debbie Keller]

RE: 20 field

Tom
I didn't mean to put down Alpha4. Actually I think it is one of the best databases I've ever used. I should have bought stock in Alpha Software because I've been the unknown salesperson for Alpha for years. I think Alpha5 is even better and have gotten all my associates to switch to it.
I had just spent 2 whole days trying to do all the saved queries that I needed and couldn't do it. After reading your last messages I see that its not going to be easy. I thought about it last night and in the morning it came to me that it would be easier to just add a field to the parent table and put a bond # or name in the field and then I just have to search for the 1 bond from the parent.
I can do this easily in shorter queries and updates.
Isn't is amazing what you think of in your sleep!
Thank you so very much for you insights and help. I actually did learn more from what you have told me. Have a really nice day.

Thank-you,
Debbie

[Tom Cone Jr]

RE: 20 field

Debbie,

That sounds better to me. And, I agree a good night's sleep often helps. It's also valuable to understand the question at hand... and that sometimes takes some work. Anyway, I'm glad to have been of assistance.

-- tom

[Jay Talbott]

RE: 20 field

Would this work?

Create a (separate, stand alone) table with the names of the people or zones you want to filter on. This may have 1, 3, 20 or 1,000 reocrds. Create an index on this field

Create a logical field in the parent table called flag

Run an operation to set all flags to .f., then run an operation to set the flags to .t. for the records which contain fields that are in the stand alone table (using key exist).

Then run the report, or query againts the logical field.

Then run an operation to set all flags back to .f. again.

In the future, modify the table as needed and repeat the above steps.

Hope this helps.

Jay Talbott

[Debbie Keller]

RE: 20 field

Thank you for your reply. I will try to do what you suggested. It sounds like it would work for me.

Thanks again
Debbie

[martinwcole]

RE: 20 field

also, you can just mark and unmark, too

[Debbie Keller]

RE: 20 field

Thanks for the info.
Debbie