Replies

The data type is Long.

I changed the position of the field MembershipLevel(which is currency data type) and that solved it.
That is very odd. I moved it in the following way. Compare it to my original code:

SELECT tblPeople.MembershipLevel, tblPeople.FirstName, tblPeople.LastName, tblPeople.Title, tblPeople.Salutation, tblPeople.Address1, tblPeople.[Apt#1], tblPeople.City1, tblPeople.State1, tblPeople.Zip1, tblPeople.ReceiveMail1, tblPeople.Company, tblPeople.RenewalMonth, tblPeople.RenewalYear
FROM tblPeople
WHERE (((tblPeople.ReceiveMail1)=”YES”) AND ((tblPeople.RenewalMonth)>8));

UNION SELECT tblPeople.MembershipLevel, tblPeople.FirstName, tblPeople.LastName, tblPeople.Title, tblPeople.Salutation, tblPeople.Address2, tblPeople.[Apt#2], tblPeople.City2, tblPeople.State2, tblPeople.Zip2, tblPeople.ReceiveMail2, tblPeople.Company, tblPeople.RenewalMonth, tblPeople.RenewalYear
FROM tblPeople
WHERE (((tblPeople.ReceiveMail2)=”YES”) AND ((tblPeople.RenewalMonth)>8))
ORDER BY tblPeople.Zip1;

All I did was move this field to the beginning of the code, away from the RenewalMonth, and it solved the problem.
(There are a few other changes in the code but they had no effect. Once I moved this field in the code, the results did not have the $ in front.)
(Another way I solved it with the original code was to change the data type of RenewalMonth from Long to Text. That also solved it. These mysteries keep me awake. uuggghhhh)

Soc

It does not appear to be that.
After trying it a number of times, opening in design, saving, running it, and adding a field in design, I finally got it to work.

However, when i tried to talk a friend thru the process over the phone, she got the same results.
Can’t figure it out.

Soc

I think I figured it out. Is it or does it sound like a CrossTab query will not return duplicate records?
The company name was the same for a number of records.
I’m going to run a find duplicates query to do some more checking but was just wondering out loud.

Soc

I see people have viewed it but no responses. Why? I will provide more info as this mystery baffles me.

1. 2 linked tables via MemberID. Primary table is tblPeople. Secondary table is tblAnnualAppealHistory.
2. tblPeople – MemberId, FirstName, LastName, Company, Telephone, etc.
3. tblAnnualAppealHistory – MemberId(foreign key) Year, Amount, Date.
4. tblPeople has 1427 records. 114 of those records are the name of a Company and therefore have no First or LastName data in their respective fields.
5. tblAnnualAppealHistory has 4 entries per record – for years 1997-2000. this is a total of 4X1427= 5708 records.
6. The base qry – qryAABaseAllMembers has the following fields selected in the design grid: MemberID, FirstName, LastName, Company, Year, Amount,Telephone, and LastNameOrCompany: IIf(IsNull([LastName]),[Company],[LastName]).
7. The results of this query work. i.e. I get 5708 records.
8. I created a Crosstab Query on top of the(recordSource) the base query. The selected fields were:FirstNamne, LastName, Telephone – these were the Row Headings.
8A) the Column headings were: Year. This gave me 4 columns with the Amount donated for each record in the corresponding year.
9. The results are not accurate. I expected to get 114 records with a blank First and LastName field, with a Telephone number and then the Amount donated for the specific Year.

Instead I get only 9 records with empty/blank First and LastName fields. It is missing 105 records.

Any ideas?

Soc

Are you saying:
1. open the form in Design view.
2. Bring up the Properties sheet
3. Click the Data Tab
4. In the Order By row type: SELECT* FROM tblAAA_History ORDER BY Year;

TIA
Soc

Should I copy down any information from my BIOS settings? Will I lose anything?

TIA
Soc

I’ve done more testing. It appears to work in my db and my version of MS Access 97. However the fix I alluded to in my previous post is not working in MS Access 2000. Any ideas?

The previous fix: open table in design; click properties; order by row = Year; This worked in my version of MS Access.

Soc

Hi,
I went into the Table Properties and in the row – Order By – I typed in year. That seemed to work, but I haven’t done more than one test.

Does that sound like it would work?

Soc

It is windows 98. I read the article. I’ll try and reboot and see what happens. Thanks
Soc

I looked at the sql but still can’t figure out anything.

As I said, I was able to create the report after I listed the columns in thecolumns heading row of the property sheet of the query.
When i did that and then ran the report wizard, the available fields showed up so I could select them for the report. Before that I could not do that.

Now my question is this: the 1st ct query I made, i did not list the columns in the column heading row of the properties list. I was able to create a report based on the ct query.

Will the fact that I have not listed the columns in the property sheet/col. headings, affect anything later on.?
Why did it work in 1 case and not the other?

Does it have to do with the prompt for criteria in 1 query and not in the other?

Soc

I found the answer but don’t know why it solved.
I opened the ct query in design view. I clicked in the background and then opened the properties list.
In the column headings row, I listed the column headings.
Everything then worked out to create the report.

Now my question is this:
In the first ct query i did not go to the properties and did not list the column headings in the col. head. row. As a matter of fact , in the 1st one, that row is still blank, but the query and associated report work fine.

Will the absence of this info cause a problem later on?

Soc

I’ll try that.

I also found out that a cross tab query does not deal with the following:

criteria row in query grid – [Enter the name of Board Member]——-for the reason that it does not recognize the [ brackets].

Have you seen that before?

Soc

Thank you so much. This site is the best. I have learned so much and look forward to more learning.

It worked like a charm.

Soc

From MS Access 97 Help File:

Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.

1 Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.

How?

2 In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
3 Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

How?

Note If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.

4 Delete the temporary table.
5 Delete the record added by the append query.
6 If you had to disable property settings in step 3, return them to their original settings.

When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.

Note If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don’t, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.

Worked perfectly. Thanks. I learned something brand new. I am learning alot.

Can you grade me on the following?

I responded to msg. 71249 with message 71294

Soc