I have a few queries getting the same data from a table, each individual query runs fine. I then union them together but what I am finding is that records containing the same data are being ignored when unioned together, how can I prevent this?

write UNION ALL instead of just "UNION"

Good man. Looks to have done the trick.

Cheers

Just Curious why would u be using a union here? What do these individuals queries look like?

Dallr

Its to do with the table structure, it has a similar data structure through out such as day1, date1, day2, date2, day3, date3 all on the same table record.
It appeared the best way to pick out querying these dates was to setup individual queries to queriey each date and union each set of results together.

(hope that made sense)

such as day1, date1, day2, date2, day3, date3 all on the same table record.

(hope that made sense)

It makes sense using a Union all for that, BUT but BUT, this reeks of a flawed design...

Though probably there is nothing to be done about it now, if you ever design a DB, please if you find you need anything repetative like this... DONT....
Even PhoneNumber, MobileNumber is a duplication which might be concidered bad form.

I agree.

Not so much bad design (I thought I was doing pretty well to get it functioning). I would say bad practice in a relational database. Although it was driven by the desire to have the output look like excel (not my choice).

pl456

this issue will be useful for future use

making something LOOK like excel doesnt mean the data has to be STORED like a spreadsheet. Presentation and storage are different things. Easy data management is far more important as far as access goes.

This is one reason (among many) why the users SHOULDNT see the tables, just forms to interact with the tables.

They are not seeing the tables, it is accessed through a form, the only way without messing with my head was to produce it this way, which I did ask about.

http://www.access-programmers.co.uk/forums/showthread.php?p=821185#post821185 (http://www.access-programmers.co.uk/forums/showthread.php?p=821185#post821185)




Just seen your signature, thats pretty funny :)

and you got a similar response, saying a spreadsheet type TABLE wasnt the best idea.

the trouble with a columnar presentation, where each column is a year is ... where do you stop - 5 years, 10 years, etc etc., and how do you store the data in your tables - do you keep adding columns for each new years, and changing your app to address the column for the new year?

thats why the normal way in a database is just to manage the data by handling a single year at a time, so you dont get issues like these.

For reports, or spreadsheet production etc, you may want to show more than one year, and you can do this, among other ways with a union query, or by creating a temporary table, or even by manually outputting (in code) a csv file.

Although these techniques can be cumbersome, its still far easier to do them as one-offs when you need them, rather than base the whole dbs in a non-normalised manner.

the look and feel of the database is very important - but as I say, the presentation of data is not the same as the underlying storage of the data.