I have sixteen tables. All are the same fields names, type and number of fields in each record. Table Named DN1T through DN16T. I need to combine all the tables periodically into table AllT and run a series of special reports. I want to execute the process with an action button. I will need to delete all records from the previous AllT table before combining the tables. Been trying to make this work all week no luck. I must be missing something.
IDAutoNumber and Key DateCDate/Time PayeeNumber Tied to Combo Box (Same Combo Payee Table used for all Tables) CategoryNumber Tied to Combo Box (Same Combo Category Table used for all Tables) ClassNumber Tied to Combo Box (Same Combo Class Table used for all Tables) DescriptionShort Text DebitCurrency CreditCurrency AmountCalculated[Credit]-[Debit] ValidatedYes/No AcctShort TextDefault Value different for each Table
Kevin Robertson 4 months ago
Why do you have 16 Tables that are exactly the same?
AmountCalculated shouldn't be stored in the table See: Calculated Fields
William Kennedy 4 months ago
Each table is a different account controlled by a different individual without access to the other accounts.
Kevin Yip 4 months ago
Hi William, if the tables have the same autonumber primary key values, they will cause duplicates in the new table. So the new table will need its own autonumber PKs.
FYI: SQL Server allows "row-level security," which forbids certain users to see certain rows. That would be a more robust solution. Consider this in case you use SQL Server in the future.
Scott Axton 4 months ago
Without knowing the structure of the rest of your database it's pretty difficult to advise you.
The issue you have now is what happens when you grow? Assuming the each table is for a sales person, what if you hire 3 more people next week? You would have to change lots of tables, forms, reports, etc.
What happens to the accounts of the DN8T records if that individual leaves next month?
The way you describe it so far your db is definitely not normalized. In addition to the Too Many Tables video Kevin gave you check out the Normalizing Data.
Check out the Access Security Seminar for more on properly setting up your data base so that users can only see their own data.
If you absolutely don't want to make the change you could always look at the Union Query. You can combine the tables but this type of query is read only. No adding or editing of records.
