Computer Learning Zone CLZ Access Excel Word Windows

For me, it is far better to grasp the Universe as it really is than to persist in delusion, however satisfying and reassuring.

-Carl Sagan
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Access >
TechHelp


Back to TechHelp
 

Append Tables issue Upload Images   Link  
William Kennedy 
52 days 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
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
52 days 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
52 days ago
Each table is a different account controlled by a different individual without access to the other accounts.
Kevin Yip
52 days 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
51 days 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
50 days ago
Use one table with account IDs.
William Kennedy
49 days 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
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.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
amicron@gmail.com
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn