"Query must have one destination field" error when opening a Table?

[newnewbie]

Please help me with this mystery. I did the Union query to put together some tables...it ran once, showing me the data. Tried to run it for the second time and it could not find one of the tables. I wanted to look at one of the other tables....and it gives me a message " Query must have at least one destination field". I am trying to open a Table. Double clicking on it.
[IMG]C:\Documents and Settings\lskalska\Desktop\table Mystery.jpg[/IMG]

Any idea, anyone?

[JustJim]

Hi,
I'm a bit worried about what you mean by "put together some tables". Can you open your Union query in design view, change the view to SQL and post the SQL here (using the code tags)? That will make it easier to see what may have happened.

Jim

[newnewbie]

Jim,
Here's the Union query code. But again, I just tried looking at a table. Union query was closed at that moment.

 Select *, #11/01/2007# as Period
from HR_0711
Union All 
Select *, #12/01/2007# as Period
from HR_0712
Union All 
Select *, #01/01/2008# as Period
from HR_0801
Union All 
Select *, #02/01/2008# as Period
from HR_0802
Union All 
Select *, #03/01/2008# as Period
from HR_0803
Union All 
Select *, #04/01/2008# as Period
from HR_0804
Union All 
Select *, #05/01/2008# as Period
from HR_0805
Union All 
Select *, #06/01/2008# as Period
from HR_0806
Union All 
Select *, #07/01/2008# as Period
from HR_0807
Union All 
Select *, #08/01/2008# as Period
from HR_0808
Union All 
Select *, #09/01/2008# as Period
from HR_0809
UNION ALL Select *, #10/01/2008# as Period
from HR_0810;
 

[JustJim]

Holy Snapdragons, Batman! Perhaps you'd better back up a step and explain what the purpose of the database application is, and what are all these HR_**** tables and what are the fields in them.

I think we might have a slight normalisation problem, but if you explain what it is that your are trying to achieve, we'll go from there.

Jim

[newnewbie]

Hehehe :) Well, those are HR tables, headcount tables. Each month I get a new spreadsheet with current headcount, name it HR_YYMM and load into access....All I am trying to do is to put all these HR tables together to see which title, etc each associate was at a certain period. That's it. It's not even that important cos I could just open each one and copy and append each month in Excel or something, but some of them I cannot open due to this "query must have at least one destination field". Now, even that does not prevent me from getting the data together because we in finance are hoarding files in all kinds of locations, so I can recover them :) But! This is the second time I am getting this weird error within Access while trying to open a table and it perplexes me.

Fields in the tables are:
EMPLID
FILE_NBR
NAME
ORIG_HIRE_DT
WORK_LOCATION
WORK_LOCATION_DESCR
REPORTING_LOCATION
LOCATION DESCR
DEPT DESCR
JOBCODE
JOBTITLE
FULL_PART_TIME
REG_TEMP
ANNUAL_RT
HOURLY_RT
GRADE
HOME_DEPARTMENT
DATA_CONTROL
COMPANY
REGION
FTE
P&L
The fields are all the same in all tables.

The database was recently converted to 2007 format. My guess is that's what caused some stuff go wacky. I am not sure. Also, is there a limit on how many tables/rows you can Union? Each HR table is around 12000 rows.

You already hinted on some normalization stuff and that's what I'm going to look up now.
I love this forum!

[NeoPa]

Check Normalisation and Table structures for Normalisation info.

NB. Posting images cannot be done on a web site with references to files on your own machine. It may work for you, but you're the only person who will ever see it as anything other than an empty box.

[NeoPa]

@newnewbie
Reading this I have no idea what you're saying you're having problems with.

If you could explain what happens when, in some sort of order, it may be clearer what you're falling over.

As to the storage of the data, I would strongly advise setting up a single table with all the data in. The date of the data is an attribute, and not something that indicates a different dataset. Simply add this into the record structure, then put all the data in together.

[newnewbie]

Thank you all, I will look at the Normalization post NeoPa referred me to and will set a single table with the data.
Thank you.

[NeoPa]

Do you get why no-one's seeing your pictures?

I noticed you'd done the same thing before, and realising it will always show as visible to you, I thought it advisable to explain.

[newnewbie]

I understand why but I do not know how to post it so that everyone sees it - if I post on flickr or similar web photo service then people will see it, right?
Thanks.

[NeoPa]

As long as the link is set up properly yes. They should do.