Results 1 to 5 of 5
  1. #1
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10

    Exclamation How to link 5 external tables to a main database and print one report of data from all of the tables

    Current situation: 5 employees enter information in individual Access databases. They print a report then turn it in to Admin who has to type in all of the information in her main database to print a report for meetings. SMH.


    What I need help with: The goal is to link all of the external tables with the Admin's database so that she can print one report using data that the 5 employees entered.

    I don't know how to link then report using data from all of the tables. The common field in all of the tables is [meetingdate]. The Admin's query should ask for the [meetingdate]

    I hope this makes sense and you can help...

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Consolidate your databases into single one. All 5 employees insert their information into same table(s), which must have some identity field(s) to to differ between them - e.g. department or employees id. Split the database to front- and back-end. Every user (5 employees and admin) must have his/her own front-end. Design a report for admin.

  3. #3
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10
    Thank you for responding so quickly. My original suggestion to the Admin was similar to your answer, just let everyone enter in the main database...simple solution; but the 5 employees want to continue entering data in "their own separate database" and the Admin doesn't want the employees to have access to her main meeting database. I know how to merge (consolidate) but I'm not familiar with splitting the database to front- and back-end. Can you direct me to instructions on how to...?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Simply design front-ends accordingly.

    All 5 employees get front-end with unbound main form which has invisible unbound text box where the value for according ID is stored. The text box must have a default value, which will be th ID value for this particular employee. Then the main form has subform(s) for data entry linked to unbond text box (subform's LinkMasterField contains unbound control's name, LinkChildField contains ID field name). User will be able to see only his/her records, and any new record he/she enters will be automatically linked to this user (his/her ID value is added into ID field of table). The control in form(s) linked to id field which identifies user must be invisible, or restricted for user.

    Admin will have a front-end without any limitations.

    When you split the database, all tables meant for use by all users are in back-end. Back-end may also contain saved queries, when they are meant to be used by all users as views. The front-end's have tables and saved queries in back-end linked. And they contain all interface (forms, reports, VBA modules, etc.). They may also have tables meant for specific user only, which are situated in front-end, and as follows, are accessible for this user only.

    There is a wizard for splitting an existing database. But you also can take an existing database, remove all interface, then split it, and you get front-end with links to back-end, but without any interface. Then you can design a new interface for front-end as you want it to be.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The design described by Arvi would be much better than what you have now, but to answer your initial question if you need a temporary solution until you redesign your database(s):
    • Open the Admin db and using the External Data/Access button on the ribbon link the five tables from the individual employees database. If they have the same name they will show up as tblMeeting,tblMeeting1,tblMeeting2,...
    • In the same admin db create an union query using the five linked tables; Note that the fields should be similar
    • Build the report using the union query as its record source

    https://support.microsoft.com/en-us/...0-ad0a75541c6e

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 12-07-2018, 04:29 PM
  2. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  3. Replies: 2
    Last Post: 04-26-2016, 05:24 PM
  4. Display/print Report with two tables
    By geraldk in forum Reports
    Replies: 5
    Last Post: 07-30-2012, 02:41 PM
  5. Replies: 30
    Last Post: 06-26-2011, 10:47 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums