OK, I am at a bit of a brain freeze, and thought that I would turn to you, my brilliant readers, for a bit of help. I have a series of unrelated tables in MS Access. They all have identical field structures, but there is no relation built into them.
I would like to construct a query that will return a result that concatenates all of these tables. I don't want to just append all of these tables together; I want to keep them separate.
Ideas? Solutions?
Subscribe to:
Post Comments (Atom)
7 comments:
Make a Table Querie
Anon; Thanks. I considered the make-table query. However, that actually concatenates the data in a new table.
I want to keep the data in separate tables, so that I can update the data in the smaller, separate tables and have that update reflected in the logically concatenated query result.
sigh.
More research; I think that the tool I need to use is a Union Query. Time to roll out my rusty SQL. I haven't had to use it since FoxPro 2.0.
Aaron, I'm not sure what you're looking to do. Are you trying to query multiple tables for a specific matches to a specific record, find records in common, or concatonate all tables and all data together into a report?
I gave up on MS Access a couple of years ago because it doesn't comply with updated SQL in certain function calls like "outside joins". Maybe that's changed by now. But my SQL chops are in pretty good shape if you can better explain what you're doing.
I use MySQL for my business data base and have written all my data entry and query screens with PHP. That way I can use both SQL and awk for reports.
One more thought. An MS Access query table is ported to a temporary object. You can even set up your SQL script or MS Access query to dump the table on close. That way you'll maintain the separate tables you currently have. If it's a straight query I don't see how it will affect your current tables at all.
Dave; The latter- I am trying to construct a report on all the tables in a particular application.
The tables all contain product data (part number, manufacturer, etc.) and the report is intended to be a "catalog" of all the products in each of the separate tables.
I want to keep the product tables separate, so that they don't become unwieldy. Also, my MySQL skills are not where they should be for the task. Plus, I have Access on my office workstation.
Anyway, the union query seems to be the right tool for the job. It would be nice if MS had better documented that you could write union queries with Access, but that is the subject of a whole different post.
Aaron,
A union query is the right tool in SQL but you may have a hard time formatting the output for the report it seems you're looking for.
I would try using the Access report function using a query table call that concatenates the various tables and then make a script that deletes the query table. In SQL you can define a "temp" table for just that function that drops at the end of the query process automatically. I think you can do the same thing in Access with their "script recorder." As I recall the one thing that Access does really well is build reports from queries calls.
Good luck with all of that. I know how databasing can be test of both logic and patience.
Dave;
Thanks! I appreciate it.
Aaron
Post a Comment