Summing records in a database

[Jonny]

I have a database with 15 identical table. How I can to sum the records in all table?

 

[Galaxiom]

Make a Union Query.

However usually when a database has 15 identical tables it means they would be better as a single table with one extra field to indicate the property that has been assigned to a table rather than a field.

 

[marianne]

1. how can you say identical?
2. why do you want to sum all the records?
3. What part of the tables you want to sum ?

 

[Jonny]

Example of the tables:

Volkswagen
Ford
Toyota
Opel
...

The fields in each table are: km, fuel, repairs, ...

How to write SQL code with UNION to sum all record of the database?

 

[Atomic Shrimp]

Create one query that does what you want, based on one table.

Switch to SQL view and if (say) your query looks something like this:

SELECT Table1.field1, Sum(Table1.field2) AS SumOffield2, Sum(Table1.field3) AS SumOffield3
FROM Table1
GROUP BY Table1.field1;

Use copy/paste to change it to something like this:

SELECT Table1.field1, Sum(Table1.field2) AS SumOffield2, Sum(Table1.field3) AS SumOffield3
FROM Table1
GROUP BY Table1.field1

UNION
SELECT Table2.field1, Sum(Table2.field2) AS SumOffield2, Sum(Table2.field3) AS SumOffield3
FROM Table2
GROUP BY Table2.field1

UNION
SELECT Table3.field1, Sum(Table3.field2) AS SumOffield2, Sum(Table3.field3) AS SumOffield3
FROM Table3
GROUP BY Table3.field1;

and so on. (Be careful of that semicolon - you only want it at the end of the whole query).

 

[Atomic Shrimp]

Alternatively, you could copy and paste one of the tables, selecting 'structure only' when you paste it - creating an empty table, then append the contents of each of your 15 tables into it in turn.

As Galaxiom says - it's nearly always better not to have multiple tables with the same structure and kind of data.

 

[Galaxiom]

Regarding the table structure.
Add a field to each table. Let's call it manufacturer. (format number)

Make a table with ManufID (format number) and ManufName (text)
Update all the records in each table so this field is populated by the ManufID corresponding with the table.

Then append all the tables into one.