Can't add columns to report's SQL statement

[Laurel]

I have a report based on a SQL statement. The report is fairly old, but now
I want to add columns to the SQL statement. Most of the time this is what
happens.

1 - I go to the SQL statement via properties/datasource (probably the only
way)

2 - I add the columns. I can see them in both the SQL View and the Design
View. Sometimes I explicitly SAVE, and sometimes I wait for the prompt to
save when I close the SQL work area.

3 - When I look at the "Existing Fields" list, the new columns are not
there.

4 - When I go back to look at the SQL, the new columns are not there.

I say "most of the time," because yesterday I was finally able to get two
new columns to "stick." I thought it was because I clicked on the "table"
field first and then chose from the list of fields above that. But today
when I use that approach to add a field it's not working. I've tried every
script I know to add columns, but nothing works to make them permanent.

Any ideas?

TIA
LAS

 

[John Spencer]

Strange this always works for me. As long as I save the new fields are
available.

What version of Access and what service packs are installed?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Laurel]

That would be great. I signed up and uploaded "Copy of tblRLC.mdb" to my
box. Not sure what to do next. How do you find it?

The report in question is rptPastorReport.
I'm trying to add the Homebound? column from each table.

I experimented with making a query of the same SQL. I was able to add the
two columns, but the one on the Spouse table showed up with lots of filled
in boxes instead of checks and empties. It's called qryPastorReport. I
forget if it still has both columns. The last time I attempted it I changed
an existing column, so it's in the middle somewhere.

Thanks in advance for your efforts and anything you can find.

LAS

 

[Laurel]

OK, I need some help here. When I click on your link I see, roughly, the
following on the web page. Where should I click? If I click download, it
looks like I'm invited to download Database_effortrevised.mdb. Looking
forward to figuring this out!

Manage and Share your files..... sign up now
Have a box account? sign up now

ShareAnyone
Folder Information

Sort by: Name
Created by June7

Change view Created on 05/25/2009

1file 4600 kb

Database_effortRevised.mdb
Download Did you know?

You need to post the link here as I did in my post. I provided a link to a
folder that you can drop your project in, was no need to create another
drop
site. Just click on the link I provided.

That would be great. I signed up and uploaded "Copy of tblRLC.mdb" to my
box. Not sure what to do next. How do you find it?

The report in question is rptPastorReport.
I'm trying to add the Homebound? column from each table.

I experimented with making a query of the same SQL. I was able to add the
two columns, but the one on the Spouse table showed up with lots of filled
in boxes instead of checks and empties. It's called qryPastorReport. I
forget if it still has both columns. The last time I attempted it I
changed
an existing column, so it's in the middle somewhere.

Thanks in advance for your efforts and anything you can find.

LAS

That is bizarre. If you want to drop your project to this site, I will
look

[quoted text clipped - 32 lines]

TIA
LAS

 

[Dirk Goldgar]

I have a report based on a SQL statement. The report is fairly old, but
now I want to add columns to the SQL statement. Most of the time this is
what happens.

1 - I go to the SQL statement via properties/datasource (probably the
only way)

2 - I add the columns. I can see them in both the SQL View and the
Design View. Sometimes I explicitly SAVE, and sometimes I wait for the
prompt to save when I close the SQL work area.

3 - When I look at the "Existing Fields" list, the new columns are not
there.

4 - When I go back to look at the SQL, the new columns are not there.

I say "most of the time," because yesterday I was finally able to get two
new columns to "stick." I thought it was because I clicked on the "table"
field first and then chose from the list of fields above that. But today
when I use that approach to add a field it's not working. I've tried
every script I know to add columns, but nothing works to make them
permanent.

Any ideas?

If yo're using Access 2007, then make sure, after you return from the query
designer to the property sheet, to click on another prooperty on the sheet
before doing anything else. I've found that Access 07 seems to lose my
recordsource changes unless I do that before closing the sheet or saving the
form.

 

[Laurel]

http://www.box.net/shared/27s7aryb01

Ummm, If you don't see an Upload button at the site then maybe I have
wrong
idea. Create your own account (the free option) at Box or some other file
share site, make a folder that is shareable, drop your file in there then
post link to it here and I will download from your folder. I haven't been
using this service long myself and thought the folder was set so others
could
upload to it. If you use Box.net then next to the folder and the uploaded
file you will see a button that says 'Share', click it and a frame showing
the url link will open. Select the link string, copy then paste in post
here.
Be sure to post the file link, not the folder.

OK, I need some help here. When I click on your link I see, roughly, the
following on the web page. Where should I click? If I click download, it
looks like I'm invited to download Database_effortrevised.mdb. Looking
forward to figuring this out!

Manage and Share your files..... sign up now
Have a box account? sign up now

ShareAnyone
Folder Information

Sort by: Name
Created by June7

Change view Created on 05/25/2009

1file 4600 kb

Database_effortRevised.mdb
Download Did you know?

You need to post the link here as I did in my post. I provided a link to
a
folder that you can drop your project in, was no need to create another

[quoted text clipped - 23 lines]

TIA
LAS

 

[Laurel]

That was it! Thanks!
I've bumped into this problem myself but hadn't thought about it in terms of
going to the query builder via the properties.

 

[Laurel]

Dirk solved the problem of why I couldn't add new columns to the query, but
I still have the problem that the second Homebound? column doesn't show
check marks. Instead it shows random grayed out boxes - not related to the
data being true or false. Could you look at this in qryPastorReport, since
you now have the database? (We hope )

tia
las

Ummm, If you don't see an Upload button at the site then maybe I have
wrong
idea. Create your own account (the free option) at Box or some other file
share site, make a folder that is shareable, drop your file in there then
post link to it here and I will download from your folder. I haven't been
using this service long myself and thought the folder was set so others
could
upload to it. If you use Box.net then next to the folder and the uploaded
file you will see a button that says 'Share', click it and a frame showing
the url link will open. Select the link string, copy then paste in post
here.
Be sure to post the file link, not the folder.

OK, I need some help here. When I click on your link I see, roughly, the
following on the web page. Where should I click? If I click download, it
looks like I'm invited to download Database_effortrevised.mdb. Looking
forward to figuring this out!

Manage and Share your files..... sign up now
Have a box account? sign up now

ShareAnyone
Folder Information

Sort by: Name
Created by June7

Change view Created on 05/25/2009

1file 4600 kb

Database_effortRevised.mdb
Download Did you know?

You need to post the link here as I did in my post. I provided a link to
a
folder that you can drop your project in, was no need to create another

[quoted text clipped - 23 lines]

TIA
LAS

 

[Laurel]

tblSpouse is actually tblRLC chosen a second time.

Maybe spoke too soon. Went to look at the tables query built from and
could
not find tblSpouse. How that query is managing to run beats me.

I did get your project file. Opened the query and all of the fields are
displaying fine, including the two Homebound? fields.

Dirk solved the problem of why I couldn't add new columns to the query,
but
I still have the problem that the second Homebound? column doesn't show

[quoted text clipped - 10 lines]

TIA
LAS

 

[Laurel]

I've put a bitmap into a Word document and uploaded it to box.net. See if
it sparks any ideas?

Also, I'd tell you what patch release I'm on in Access, but I can't find it.
Why did Microsoft abandon universally understood HELP/ABOUT??????

I did get your project file. Opened the query and all of the fields are
displaying fine, including the two Homebound? fields.

Dirk solved the problem of why I couldn't add new columns to the query,
but
I still have the problem that the second Homebound? column doesn't show
check marks. Instead it shows random grayed out boxes - not related to
the
data being true or false. Could you look at this in qryPastorReport,
since
you now have the database? (We hope )

tia
las

Ummm, If you don't see an Upload button at the site then maybe I have
wrong

[quoted text clipped - 36 lines]

TIA
LAS

 

[Laurel]

http://www.box.net/shared/tj0ydelb5y

I need the link to the image file. It doesn't appear to be in my Box.net
ShareAnyOne.

I searched Access help for 'about Access' and got the following topic
(mine
shows SP1 in version info):
Get the version number for your Office program and information about your
computer:
1. Click the Microsoft Office Button, and then click Access Options.
2. Click Resources, and then click About.
3. To see information about your computer, click System Info.

I've put a bitmap into a Word document and uploaded it to box.net. See if
it sparks any ideas?

Also, I'd tell you what patch release I'm on in Access, but I can't find
it.
Why did Microsoft abandon universally understood HELP/ABOUT??????

I did get your project file. Opened the query and all of the fields are
displaying fine, including the two Homebound? fields.

[quoted text clipped - 16 lines]

TIA
LAS

 

[Laurel]

Thanks for the suggestions about things to try. Will do.

I have the image. See what you mean by grayed out. Odd some are, some
aren't.
Very difficult to debug something when you can't replicate the problem,
which
is the case here because the boxes show fine for me. Things to try:
Opening
on other machines; create a new project, import the tables and this query
into it; create new project, create tables and query from scratch;
reinstall
Access.

This 'grayed out' effect is like what you would see in Excel when you have
multiple cells selected with different property settings, such as Wrap,
and
then you view the properties window and the Wrap check box is gray,
indicating the property setting is not the same for all the selected
cells.
However, at a loss as to why this would happen in an Access query. Will
look
at it again when I get home, but as I said, not seeing the problem. At
this
point suggest you might want to start a new thread with an appropriate
title
to get new perspectives.

http://www.box.net/shared/tj0ydelb5y

I need the link to the image file. It doesn't appear to be in my Box.net
ShareAnyOne.

[quoted text clipped - 20 lines]

TIA
LAS

 

[Laurel]

Where do I look to find the patch level of my Access?

I have the image. See what you mean by grayed out. Odd some are, some
aren't.
Very difficult to debug something when you can't replicate the problem,
which
is the case here because the boxes show fine for me. Things to try:
Opening
on other machines; create a new project, import the tables and this query
into it; create new project, create tables and query from scratch;
reinstall
Access.

This 'grayed out' effect is like what you would see in Excel when you have
multiple cells selected with different property settings, such as Wrap,
and
then you view the properties window and the Wrap check box is gray,
indicating the property setting is not the same for all the selected
cells.
However, at a loss as to why this would happen in an Access query. Will
look
at it again when I get home, but as I said, not seeing the problem. At
this
point suggest you might want to start a new thread with an appropriate
title
to get new perspectives.

http://www.box.net/shared/tj0ydelb5y

I need the link to the image file. It doesn't appear to be in my Box.net
ShareAnyOne.

[quoted text clipped - 20 lines]

TIA
LAS