Computer Learning Zone CLZ Access Excel Word Windows

Sometimes, the only thing more dangerous than a question is an answer.

-208th Rule of Acquisition
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Access >

Back to TechHelp

Append Tables issue Upload Images   Link  
William Kennedy 
4 months ago
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
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
AcctShort TextDefault Value different for each Table
Kevin Robertson
4 months ago
Why do you have 16 Tables that are exactly the same?

Too Many Tables

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.
Richard Rost
4 months ago
Use one table with account IDs.
William Kennedy
4 months ago
That is what I am trying now.  Is it possible to generate a Running Balance using the Date Field rather then the ID field?

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to TechHelp Comments

Start a NEW Conversation
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
Subscribe to TechHelp
Get notifications when this page is updated

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard

Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Copyright 2023 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/3/2023 10:13:46 PM.