Need Help Multiple Table Queries

[ShredYou]

I cannot figure out for the life of me to get this to work, I have multiple tables and all tables have the same fields, Nomenclature, Name, NSN, Platoon, ECT. Under Platoon in each table i have 4 diff platoons Ops, 1st, 2nd, 3rd. I want to pull info from all tables based off of the platoon. This is for a weapons system database. I have a table for each weapon system that gives the info then it is sorted by the platoon field. I need the info from all the fields just sorted by the relevant platoon. So I have an m16 table with all fields listed above and an m4 table with all fields listed above. I need to figure out how to combine all the tables then make it show the items for a single Platoon from all the tables so i can make a report.



I am so frustrated right now i probably sound retarded but im getting sick of trial and error so this is my last resort.

[ShredYou]

Let me rephrase this. I have a apples table and an oranges table each table has fields describing the apples and oranges. Imagine one field is a serial number and another field is owner. I need to combine apples and oranges tables then filter by a specific owner to get rid of the owners i dont need at the time. I hope this makes more sense.

[June7]

Tables having identical fields should be one table with another field to identify category (m16, m4, etc). Combine tables with UNION. Following assumes fields are identical names and in same order in all tables.

SELECT * FROM m4
UNION ALL SELECT * FROM m16
UNION ALL ...;

There is no wizard or designer for UNION query, must type into the SQL view window of the designer. Save the query and then use it as the source for other queries.

Better, get your data in proper structure.

[ShredYou]

I know what your saying about making it one table but I have to generate reports from each weapon system and if you saw how many entries it would be for one table it would be extremely hard to follow. I dont know how to use the SQL so I guess my only solution is to copy and paste from each table the info by platoon into a Platoon table, which will be terribly time consuming.

[June7]

I don't understand how the number of records in a single table is an issue. Report would have the table as its RecordSource and filter criteria in the report would restrict the records.

Improper data structure will continue to give you major headaches.

You also have choice to learn SQL syntax and use. Not that difficult.

[ShredYou]

Ok so your saying to make a giant table since all weapons systems have the same fields just different entries, then to auto populate my reports just filter it by the field that has the weapon name and the field that has the specific platoon? Sorry if this sounds retarded but i just started using this program a couple of days ago.

[June7]

Yes, that is correct.

Can simply set the form/report RecordSource property to the table and put criteria in the form/report Filter property, FilterOnLoad property to Yes

Or RecordSource can be a SELECT query. Use the Access query designer or wizard to help build query. Use query as the
RecordSource for a form or report. Access queries use SQL statements. This will also help you learn SQL as you can view the statement constructed by Access. Filter criteria can be in the query or again in the form/report properties.

Use report Grouping & Sorting with summary calcs to organize data on report.

Access Help has guidelines.

[ShredYou]

Ok I did what you said and it worked I appreciate the help. Much easier to work with everything on one table and just filter what you want out and make a report.