By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

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

newnewbie
P: 54
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?
Apr 14 '09 #1
Share this Question
Share on Google+
10 Replies


JustJim
Expert 100+
P: 407
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
Apr 14 '09 #2

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

Expand|Select|Wrap|Line Numbers
  1.  Select *, #11/01/2007# as Period
  2. from HR_0711
  3. Union All 
  4. Select *, #12/01/2007# as Period
  5. from HR_0712
  6. Union All 
  7. Select *, #01/01/2008# as Period
  8. from HR_0801
  9. Union All 
  10. Select *, #02/01/2008# as Period
  11. from HR_0802
  12. Union All 
  13. Select *, #03/01/2008# as Period
  14. from HR_0803
  15. Union All 
  16. Select *, #04/01/2008# as Period
  17. from HR_0804
  18. Union All 
  19. Select *, #05/01/2008# as Period
  20. from HR_0805
  21. Union All 
  22. Select *, #06/01/2008# as Period
  23. from HR_0806
  24. Union All 
  25. Select *, #07/01/2008# as Period
  26. from HR_0807
  27. Union All 
  28. Select *, #08/01/2008# as Period
  29. from HR_0808
  30. Union All 
  31. Select *, #09/01/2008# as Period
  32. from HR_0809
  33. UNION ALL Select *, #10/01/2008# as Period
  34. from HR_0810;
  35.  
Apr 15 '09 #3

JustJim
Expert 100+
P: 407
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
Apr 15 '09 #4

newnewbie
P: 54
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!
Apr 15 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
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.
Apr 15 '09 #6

NeoPa
Expert Mod 15k+
P: 31,709
@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.
Apr 15 '09 #7

newnewbie
P: 54
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.
Apr 15 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
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.
Apr 15 '09 #9

newnewbie
P: 54
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.
Apr 15 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
As long as the link is set up properly yes. They should do.
Apr 15 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.