QUERY ISSUE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HELP!!! I have an existing Access document that I work with on a daily basis.
Somehow, I've managed to modify it and am not able to print detail reports.
The overall report works fine. Two of the tables are missing two columns - I
know which ones. I cannot figure out how to correct the problem (ie, how to
go in and add the columns to each of the two tables. Here's the message I
keep getting:
the number of columns in the two selected tables or queries of a union query
do not match. I would appreciate anyone's assistance on this problem. Thanks
in advance :)
 
Dear Georgia:

There are some essential things to be done:

1. Do not work in the "live" copy of the database.

2. Keep regular backups of the live database.

3. Especially, back up the "old" database before installing changes
you have made.

Did you do any of these things? Do you have important data that is
missing and of which you have no copy anywhere?

If you have loss of data and no backup, make an immediate backup
before doing anything. There is some expertise available that MAY be
able to recover what you have lost, but the more new work you do, the
less likely it can be recovered. You will need this immediate backup
to maximize that possibility. Further work on the existing database
will likely overwrite some of what you may want to recover.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
HELP!!! I have an existing Access document that I work with on a daily basis.
Somehow, I've managed to modify it and am not able to print detail reports.
The overall report works fine. Two of the tables are missing two columns - I
know which ones. I cannot figure out how to correct the problem (ie, how to
go in and add the columns to each of the two tables. Here's the message I
keep getting:
the number of columns in the two selected tables or queries of a union query
do not match. I would appreciate anyone's assistance on this problem. Thanks
in advance :)
Click to expand...

It looks like you've been bit by the Microsoft website newsreader bug
which falsely tells you that your message failed to post: it did,
three times! Ignore the message in the future (it has to do with email
notification, not with actual posting).

THat said... take a look at the Report in design view. View its
Properties; find the Recordsource property; and click on the ... icon
to open it in design view. This will probably be the SQL view of the
UNION query, which will look something like

SELECT thisfield, thatfield, anotherfield FROM onetable
UNION
SELECT somefield, whatfield, leftfield FROM anothertable;

If you've changed the structure of the tables, you'll need to make
corresponding changes in the UNION query. Every SELECT clause in the
UNION must have the same number of fields, and they must match in
datatype as well. If you have (say) eight fields in one table, and six
fields in another, you must either remove the two fields which don't
match up, or edit the smaller table's SELECT clause to insert blank
fields to line up:

SELECT field1, field2, field3, field4, field5, field6, field7, field8
FROM bigtable
UNION
SELECT field1, field2, NULL As field3, field4, field5, field6, field7,
NULL as field8
FROM smallertable;

the "as field3" isn't really necessary but it might make the query
easier to understand later!
i
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top