Query Operations

[Tom Henkel]

Re: Query Operations

Kay,
I downloaded your files and viewed the set browse. I am NOT getting the repeats as you seem to have. see attached.

What update version are you running? I'm running 3345-3595.

specifically, what query were you running???

Tom

[KarenABedeau]

Re: Query Operations

Tom, Mike & Stan.
Sorry for the delay in response. My email stopped telling me there were responses on this message board, so I just came over to look and found your three.

Here is what I am trying to do:

Query the set for:
Verify is between = 10-O01 and 10-O40
(an alternative would be for Trial_Date is in the month of October)
and
Trial_Date is in the Year 2010.

When I run this query and browse the resulting table, the records are very strange. The part of the record that comes from the parent database (scores) is for only one record and is repeated multiple times. However, for each of these records, the fields that come from the child databases are correct.

When I use this query for a Report, the records seem to be correct, except all of the possibilities for CLASS are not included. Namely BNA, BNB, VER, GN and GO are all omitted.
I can't see anywhere that these possibilities have been filtered out.

Could the missing classes in my Report, and the multiple records in the Query be related? I don't think so, but both problems are preventing me from using reports. With 11,000+ records in the set, it makes handling the data next to impossible.

Do you want me to send you queries and reports?

Kay

[KarenABedeau]

Re: Query Operations

Tom,
I am also running 3345-3595

Perhaps there is something wrong with the way Iam designing my query. I use the Genie:
Condition 1: Trial_date is in the year 2010
Condition 2: verify is between 10-O00 and 10-O99
Order is: first = Trial_date, second =club, third=verify

Attached is a screen shot of the browse that results when I run my query. It's in three pieces as I move to the right.
Note the repeated information comes from the parent database (scores), but the repeats are not in the child databases (irish and trials).

I'm not even going to go into what happens with the field (class) in my reports at this point. One problem at a time.

Kay

[KarenABedeau]

Re: Query Operations

Sorry...the attachment is here.
Kay

[Mike Wilson]

Re: Query Operations

Kay,
Read post #5 in this thread again. There is nothing strange in what is presented. Each "child record" when viewing a set is a composite of the parent and the child. What exactly are you expecting to see?

[KarenABedeau]

Re: Query Operations

Mike,
Are you not seeing that there is a whole string of records in the query browse with repeated information in the left hand records and not repeated information in the right? Am I losing my mind?
My set has 11,700+ records.

I want to query (and report) on records only with verify between 10-O00 and 10-O99, and trial_date in the year 2010. When I do this, I still get a browse table with 11,700+ records.

Dog ID for all records on the first page of the browse is 784 and Trial ID is 6980, and class is OB. Dog #784 was in Class OB at Trial 6980 only one time, as can be seen in the table scores, when viewed as not part of the set. When I move the cursor on these fields in the query browse the numbers change, but Trial ID, Dog ID, and the other source table fields are still repeated for more than a whole page. Look at the snapshots (3 to get the whole first page) I sent a few moments ago.

Kay

[KarenABedeau]

Re: Query Operations

Mike,
I just figured out that my query does not have 11,700+ records...
The set has 11,867 records, as noted on the lower right of my screen.

The browse that results from my October 2010 query has 44 records,which is not correct...there should be 68. (I counted them in the full set browse)

It is not possible to scroll down and count the records in the browse (I read the number at the lower right screen), because when I start scrolling, the numbers for Dog-ID, Trial-ID change. That is, when the browse first comes up after I RUN the operation (query on verify between 10-O00 and 10-O99, and year = 2010) the first page I see has identical Dog#, Trial#, and class for each record. (Note: Scores has only ONE record for each particular combination of dog, trial # and class...there should NOT be multiple child records for any one Score record).
As soon as I move the cursor from the top left of the browse, the (identical) dog#s and other fields change to a different number, but still all repeated. It's so frustrating that I can't explain exactly what's happening, but I know it's not right. It didn't do this in V7. It doesn't do this when I browse the entire set. Only when I run my queries.

I don't get the repeats in my reports, even when I specify a filter criteria query that gives me the repeats in the browse. I have a different problem with my reports re the CLASS field, but I can't get my mind around working on both problems at once.

Kay

[martinwcole]

Re: Query Operations

Karen, when you open a browse based on a set, here is the way it works:

say the parent table name is "parent"
the 1st child tablename is "child1", and the 2nd child name is "child2"

lets say the the first parent record has 2 child1 records, and 3 child2 records

you will see 5 lines in the browse

1. parent fields + child1, record 1 fields
2. parent fields + child1, record 2 fields
3. parent fields + child2, record 1 fields
4. parent fields + child2, record 2 fields
5. parent fields + child2, record 3 fields

in a report you see the parent once, and the children as many times as they have records

[Mike Wilson]

Re: Query Operations

Kay,
Read post #5 in this thread. Because your set links the tables all 1:1, its no different than having all the data in one table. I am not sure what you are expecting. Download the attached example and look at the form Fs_obd_scores which has an embedded browse for each of the tables in your set and see if this might clears things up for you. There is a filter button so you can filter the Scores table based on the verify range as you wrote you might do and see the results. I think you might very well be confussed about what should be happening.

[KarenABedeau]

Re: Query Operations

The parent tablename in my set is "scores"
The first child tablename is "irish" and the second is "trial"

Each parent record (record in scores) has one and only one
record from the first child table (irish) and one and only one record from the second child table (trial).

Look at it this way. We are talking dog shows here:
Scores has a record that a dog (Dog_ID#) earned a certain score in a given class (and all the stuff that goes with it)...points/judge/page of book recorded on/etc. Child table #1 provides the full name of the dog/owner/titles/years competing/etc. Child table #2 provides the club that hosted the trial, date of the trial, city and state where the trial was held.

So, you see, there can't be more than one record from either child table associated with each record in the parent (scores).
Each score earned that appears in the parent table is earned by only one dog in the child table irish and at one trial in the child table trial.

When I do a browse on my full set table, I get one record for each record in scores, along with it's associate dog and trial information. I can scroll through this browse (11,000+ records) to proofread the book I happen to be working on. Or print a report. But, when I do a query to filter, so I get only scores from the October book from the year 2010, I get the repeats, and a table that behaves strangely when I move the cursor. The report seems to print OK, except it omits records for some of the class entries, namely GO, GN, BNB, and VER. There must be something I can't find in my report design that causes this, but this is a different problem.

I hope this helps explain my situation.
Kay

Karen, when you open a browse based on a set, here is the way it works:

say the parent table name is "parent"
the 1st child table name is "child1", and the 2nd child name is "child2"

lets say the the first parent record has 2 child1 records, and 3 child2 records

you will see 5 lines in the browse

1. parent fields + child1, record 1 fields
2. parent fields + child1, record 2 fields
3. parent fields + child2, record 1 fields
4. parent fields + child2, record 2 fields
5. parent fields + child2, record 3 fields

in a report you see the parent once, and the children as many times as they have records

[KarenABedeau]

Re: Query Operations

Kay,
Read post #5 in this thread. Because your set links the tables all 1:1, its no different than having all the data in one table. I am not sure what you are expecting. Download the attached example and look at the form Fs_obd_scores which has an embedded browse for each of the tables in your set and see if this might clears things up for you. There is a filter button so you can filter the Scores table based on the verify range as you wrote you might do and see the results. I think you might very well be confused about what should be happening.

OK, here's Post #5:
Kay,

When you browse a set, you are looking at composite information. If a parent record has many child records, you will see one parent for each child, so it will look like you have all those parent records.

Don't give up on Alpha, there are many here who are more than willing to help you get the results you are looking for.

Tom

Mike,

YES, I understand that my set it like having just one table. The only reason to not have one table is to not have to re-type all of the dog information and all of the trial information each time it is used for a new score.

I am expecting to be able to run a query on the set and each record in scores that satisfied the query criteria appear in the resulting browse exactly once with the correct record from irish and trial. Instead I get the score information repeated in many records, each with the same Dog ID and Trial ID, but scrolling to the right, the Dog and Trial names do not agree.

How do I use your zipped file? I tried unzipping it but don't know how to get it into my Alpha5 program and to look at it...is
it a separate database?

Thanks,
Kay

[Mike Wilson]

Re: Query Operations

I believe what you are wanting is the links to be 1:M even though there is only one linked record. The attached example is an update to the last one I placed. The new one has 2 sets, one is the original 1:1 and the second I set to 1:M with new forms. I believe the views you are looking for is 1:M.

Simply Unzip the downloaded file, and open the .abd file within the new folder the unzip should create. Yes it is a completely separate database.

[KarenABedeau]

Re: Query Operations

I believe what you are wanting is the links to be 1:M even though there is only one linked record. The attached example is an update to the last one I placed. The new one has 2 sets, one is the original 1:1 and the second I set to 1:M with new forms. I believe the views you are looking for is 1:M.

Simply Unzip the downloaded file, and open the .abd file within the new folder the unzip should create. Yes it is a completely separate database.

I got it unzipped and got to A5V10 with tables with ? marks on each one. Where do I go from here?

[Raymond Lyons]

Re: Query Operations

I got it unzipped and got to A5V10 with tables with ? marks on each one. Where do I go from here?

Kay, I unzipped Mike's file and opened it in v10.5 and in v9 with no problems. Don't know what to make of the "tables with ? marks on each one."

Raymond Lyons

[Bookkeeper]

Re: Query Operations

Hi Kay

I have unzipped Mikes file ok with no problems and I have 10.5 also. The tables with a blue ? on them usually means that the data(table)files with the .dbf extension are not in the location where your database is looking for them. I usually see it in my programs if I have deleted temp dbf files from windows (bad policy I know) and not from the database.

As Mike said create a new temp folder in your windows version and unzipping Mikes complete database files to it. It's a great little program and I would think it gives the results you are looking for and will help solve your problems.

Best of luck.