Do Linked Tables Update Automatically Access

You can link only to tables in another Access database. I know both the locations of the MDBs and the table names where they reside. For instance, if a user opens a table datasheet and sees a column of company names, what is in the table is, in fact, a numeric CompanyID, and the table is linked with a select statement to the company table by that ID. Please note that Access already automatically linked the Orders table with the Products table using the common field: 'Product ID' (5) To link the remaining tables together, you'll need to drag the common fields under the Orders table, and then place them on top of the corresponding fields under the other tables. I'll cover the following topics in the code samples below: ADODB. The lookup method is used to link one group to another group to avoid the duplication. Tables: Relink Access tables from code. Everything works fine when you first link it. The Access linked table is just a view of the data in Excel. Recreate the linked tables. If you have multiple tables of data in a file and you want to view a single report based on these tables then it can be time consuming and risky to manually copy and paste them into a single table before creating pivot table. As you can see, the updated table lost its header settings. Create a Modified Audit Trail Table This version of the Audit Trail tool, in addition to recording edits to existing data, records details of additions to and deletions from the database. So, how do we execute an UPDATE and APPEND query at the same time? Well, we also start out by linking to the Excel spreadsheet to access the data. Open the Access database. How To Disable Automatic Update Of Links in Excel. "The Met Police Press Bureau uses an online provider called MyNewsDesk to. Links in the table of contents will automatically make this encoding so the url can be copied from there. If this doesn't sound 100% thrilling to you, you haven't stopped to consider how linking Excel to Word or PowerPoint will save you a billion hours of your life (and impress your boss and get. Relink tables in multiple back-end Access databases. See for yourself why shoppers love our selection and award-winning customer service. The only other paste link options I found that maintain the semblance of my Excel table, is a) Link and Keep Source Formatting, and b) Link and Use Destination Styles. I've used this with Access and MySQL databases, using the Access interface to view and update MySQL tables, since I haven't found an editable table view in MySQL query browser that functions like the one in either Access or SQLServer Mgmt Studio. From the Data Source dropdown box choose Main 5. The tab headers aren’t in all CAPS anymore. Would anyone be able to advise on how to do this? or if it is possible?. possible to update a linked text table. TOC styles are set to update automatically, so you can format your TOC 1 paragraph as bold, and that will update the style. To create a linked table:. Is there a way to make all tables imported from MS Access automatically update upon load? I'm trying to make this as simple as possible for end users, and don't want them to have to manually refresh the DB data. 1, “Installation Sources” for details. I am not fluent in technical jargon so if you could answer with something step-by-step (like 1. Linked objects, on the other hand, do. way to add a code that would automatically refresh link tables. On your Apple TV HD or 4K, go to Settings > Apps. Julie asked whether a procedure could be developed to update the chart automatically. Access will display a success message once the linked tables have been refreshed. Id like it to automatically update, leaving all current data in the table in the database. I have a folder say with 100 pdf's. Adding new tables. An example is an Employee_ID that is equivalent to an autonumber field. Combine Excel worksheets by matching columns. Word does not automatically update the Table of Contents as you type, but if you make changes to your document and need to update the Table, you can do this quickly by selecting/highlighting your Table of Contents, and then going to the "References" tab on the menu and clicking the "Update Table" button next to "Table of Contents". I am trying to write a macro that is attached to a button, which will refresh the data automatically. You can use Excel spreadsheets to manipulate and assess the data that is kept in an Access table. You could create a table of contents manually, but it would be a real waste of time. Does not require you enter or store database or table names. What you should do - especially if you know that you have to do the import multiple times with actualized data - is to automate the whole process. If the SQL Server or SQL Azure table definitions are altered, you can unlink and then re-link the tables in SSMA by using the procedures shown previously in this topic. Select Get Updates. If they aren't visible, choose browse and look in Library. Conclusion. Eventbrite - On The Table Central Georgia presents On The Table Macon: Access to Affordable Healthcare - Wednesday, October 30, 2019 at Congregation Sha'arey Israel, Macon, GA. In this figure you can see the Clients Table is open in the Access work area. You do that by following these steps: Click the Database Tools tab on the Ribbon. The employee has relationship or links to the job or task that he or she performs. As we know, we can import or export data between Ms Access and Ms Excel. Cell linking is useful when you want to consolidate information from multiple sheets. To turn off auto-updates of Google Chrome on Windows, you need to instruct Google Update to not update it. Right-click on “MyContacts” and choose Repeating Table – this will add a repeating table bound to that connection to your View 4. Question: In Microsoft Access 2007, how can I refresh all table links without having to delete the link and recreate it? Answer: To do this, under the Tools menu, right-click on the linked table in the left window. Start the Linked Table Manager feature. In this article, you will learn how to disable the automatic update of links in excel. A Table of Contents is a simple yet extremely useful feature in any Excel workbook that contains more than a few sheets. Provided by Peter Vukovic. Julie asked whether a procedure could be developed to update the chart automatically. When a record in a parent table is deleted, Access deletes all the related records from the child table where the value from the primary key matches the value in the foreign key. Account Settings. After updating to RHEL 7. Excel File Links - Find & Break Broken Links (de-link) Advanced Excel users sometimes link up several Excel files to form a reporting package. If you try to put in text, Access won't like it and it will let you know. Microsoft Access - How to update linked tables Open your Access database. , but server logistics can sometimes get in the way. When a staging table needs to have the same structure as an existing table, this can only be accomplished by writing some VBA code to do what you can manually do by using Copy and Paste. The addition of linked tables to a Power Pivot database provide an easy and flexible means for imposing organization and grouping on data obtained from research groups in Excel workbook format. Creating Word documents containing List fields I haven’t seen much information on the web regarding how to automatically create Word documents in SharePoint and how to pass list item fields directly into a document. Otherwise, the table’s columns will not show up in Expression Builder later. CommandSQLServer 2000, SQL Server, Excel 2007, Accounting, and Intersect. Most user's see this as a glaring lack of functionality. When you link to a table in an Access database, Access creates a new table, called a linked table, which maintains a link to the source records and fields. Become more productive - Learn Excel Defined Tables. I now have a situation where I've created a form based on a multi-table query in which I need to update some of the fields. There are few ways to link tables in Access 2016… In this post, I am going to show you how to link tables in Access 2016 using an Inner Join. I have a tool that references linked tables from 2 other tables. Do Linked Tables in Access 2010 update automatically just by opening the database ? I'm using Access 2010 and I have a table linked to an Excel file stored on the company network. A Linked Table Manager window should appear as follows: Select the tables. If you find you do not need a service, place it into Manual or Disabled. Link Tables Dialog. To configure access to tables and views, you grant a Cloud IAM role to an entity at the dataset level or higher. We use 2013 at work and it does seem better in the newer versions. Up to now, I have had pretty straightforward forms that basically relate to one table. To fix this, use the Linked Table Manager the same way as in the above example. The question mark icon for help has been removed and replaced with a Light Bulb icon with a search field: Tell me what you want to do… This is actually a whole new feature. Now, whenever you add new data into you datasheet it will automatically update pivot table range and you just have to refresh your pivot table. You cannot link to queries, forms, reports, macros, or modules. I created an Microsoft Access front end that points to SQL Server using linked tables, but now I need to change which database the data is being pulled from. Microsoft Access - How to update linked tables Open your Access database. You may have to register before you can post: click the register link above to proceed. Does anyone know how to do this where the data will automatically update the tables will new data or just re populate the data with an event?. A linked record field allows you to represent the relationships between related records by creating links between them. possible to update a linked text table. If you want to stay informed of my updates on this topic and other articles, click the "Follow" tab at the top of this article. MS Access Forms MS Access Queries MS Access Report MS Access Tables 14 responses on " MS Access - Listing of Database Objects (Tables, Queries, Forms, Reports, …) " Grambo September 5, 2010 at 4:44 am. Let us click on the plus sign and you will see the information that is related to this record is on the tblHRData table. Ranked by The Verge as the world's best to do list app. MDB and PRG. Update excel chart object on form - Microsoft Access. Click the link you wish to update. However, keep in mind that if you do so and then later choose to update the table of contents, you'll lose all your formatting. In all formats, if you do not specify a file name but only a directory, the installation program will look for a file named updates. I used to update linked spreadsheets from a query in access with no problems in an earlier version of access (97). I'm hoping to be able to grab some other data from some of fivethirtyeight's other graphics, but this is the most important one that I would like to have. way to add a code that would automatically refresh link tables. How to Link Tables in Access. Instagram update introduces support for iOS 13 dark mode. Recent Posts. Go to Excel, right click on the chart and Copy 3. I can't use the link object function under special paste because it needs to be part of the table. Convert Data into a Table After Creating a Pivot Table If you already have a pivot table in your worksheet. In this tutorial, we discuss about how to update Access Database from Excel. To smoothly animate changes in data values, create loops that execute two times per second or less. Automatic Mail Merge With VBA and Access November 23, 2015 by Ryan McCormick 18 Comments I once built a program with MS Access that handled a mail merge job of ~1000 letters per week. View case status online using your receipt number, which can be found on notices that you may have received from USCIS. Music listeners on Android devices are now just a few taps away from streaming their go-to tracks and discovering new music. For multiple-table syntax, ORDER BY and LIMIT cannot be used. After updating to RHEL 7. The syntax is the same as in the inst. It allows you to Refresh your Pivot Tables as soon as you open up your Excel workbook. I have my table and on it a have a list of names. What you should do - especially if you know that you have to do the import multiple times with actualized data - is to automate the whole process. you could simply have your data in an Access file, while this file is stored on a SharePoint server, simultaneously you can use an other Access file, that has linked tables into the primary Access file. Doing so displays them in MS Query, as shown here. Please do as this: 1. MDB and PRG. Update your browser to view this website correctly. If in the backend of your Access Database, linked tables are present and you move the backend database then the frontend database stops working properly. If not already automatically done by Access, drag the Product ID field under the Orders table into the Product ID field under the Products table You have now linked your 4 tables together. Go to Excel, right click on the chart and Copy 3. Relink tables when the databases are moved to a different folder or a different computer. How to update advanced filter result automatically in Excel? When we apply the Advanced Filter feature, we will find that the filtered result will not change automatically with the criteria change. Set the relative path of new links By default, Dreamweaver creates links to other pages in your site using document-relative paths. Click to start the download. Cascade Update Related Fields. Here Mudassar Ahmed Khan has explained with simple examples, how to write Insert, Update and Delete Triggers in SQL Server. The lookup method is used to link one group to another group to avoid the duplication. Basically, there are 3 ways to duplicate a table in Access by using VBA code and there are pros and cons for each. In Word, tables of contents rely on your use of styles to format headings. Show us what you have for code so far and we can tune these suggestions. From the Relationships group, click the Relationships button. You can use forms to control access to data, such as which fields of data are displayed. Method 1 – Automatically refresh pivot table when opening the file. A few key things to remember when drawing the relationship lines:. As one PowerPoint newsgroup visitor put it: "I have a presentation in MS PowerPoint 97. I'm working with a legacy Microsoft Access database that we've recently updated to use linked tables/SQL Server backend. Go to Excel, right click on the chart and Copy 3. How to Link Your SharePoint List to Excel: SharePoint provides the capability to Link your SharePoint list data to an Excel workbook. I have linked several tables from one database into another. Step 3: Now we have the entire table contents in the record set. This has the singular disadvantage of having to be updated manually, unlike the TableDefs collection whose membership is maintained by Microsoft Access. You can set Word to always update cross-references before printing your document. Access, create, and edit your presentations wherever you go — from your phone, tablet, or computer — even when there’s no connection. The linkdata function updates linked data sources and charts around twice a second. This means that you can dump data in one location and then have a table update itself automatically, which in turn updates a graph automatically. HTML preprocessors can make writing HTML more powerful or convenient. To learn more about an app before you download it, look at screen shots, read the description, content rating and any user reviews, and do some research on the developer. Become more productive – Learn Excel Defined Tables. Creating Word documents containing List fields I haven’t seen much information on the web regarding how to automatically create Word documents in SharePoint and how to pass list item fields directly into a document. To do this, you can either: Use the Google Update ADM templates provided on this page or as described in this article. Manage Remote Backend MS Access Database Programmatically With VB Code. Update Query Option when Designing Queries in MS Access 2013 and 2016. The tables in Access are ODBC linked to an AS400 database. Auto update a chart after entering new data with creating a table. Word has never had such an option, but has had an option to update links automatically. This is particularly helpful when you have multiple tables of related items or concepts—for example, if you have a table of contacts and a table of companies, you can use a linked record field to link each contact to the company that they work for. MS-Access Tips for Serious Users. Right-click on “MyContacts” and choose Repeating Table – this will add a repeating table bound to that connection to your View 4. For example, if you change a customer's ID in the Customers table, the CustomerID. How to auto-refresh pivot table. Copy the table from the example workbook, and head back to Microsoft Word. Introduction. For all its data processing abilities, the pivot table relies upon the OLAP cube. From the Relationships group, click the Relationships button. Automatically adding date/time stamp to an edited record - Microsoft Access. " You may also want to see the sample code from The Access Web by Dev Ashish using the RefreshLink Method. It works only with Access 2000 or later, but earlier versions do not have this problem. In this post we will guide you on creating a table field having Hyperlink data type. For each table create an Append SQL Query to copy the data from the link table into the new table. Linked content makes it easier to support emerging workflows, where for example, you design for vertical and horizontal layouts. Here Mudassar Ahmed Khan has explained with simple examples, how to write Insert, Update and Delete Triggers in SQL Server. Turnitin solutions promote academic integrity, streamline grading and feedback, deter plagiarism, and improve student outcomes. Show us what you have for code so far and we can tune these suggestions. You can still do the "Update Link" thing, but I do think that it should recognize changes automatically. Select Help. Update Queries let you modify the values of a field or fields in a table. 55 HF-007: TS-46876 - A data table created from an existing on-demand data table with added rows, does not get automatically updated when the on-demand data table changes. I want to have the generic ballot automatically update in excel from this link. To disable Java Update, deselect the Check for Updates Automatically check box. In order to create the relationship you first need two tables. I'll just try to explain what I am trying to do and if anyone can help or give some advice it will be very much appreciated. How do I create a clickable Table of Contents that will link to a page in Adobe Acrobat Pro DC. Get Linked Table Manager in Access 2010/2013/2016/2019 Ribbon if you do not have Classic Menu The Access 2010/2013/2016/2019 Ribbon layout is different from that of Access 2007. TAGs: SQL Server. There are numerous fields a user wouldn't want to update automatically, including ASK & FILLIN fields. Otherwise, the table’s columns will not show up in Expression Builder later. However, I’ve never seen a description of how to do it. Office 2013 updates, August 2018 KB3172506 – Update for Microsoft Office 2013 that fixes an update issue for new meeting locations when using the Rooms button or the Room Finder. Enter a name for your calculated field in the first empty row of the column names. These allow you to tell Access to. When you want to add a new column to your data table and populate those records with something, you need to use an Update query in Access. (In this example, we want the Books field in the Authors table to link to the Books table, so we select the Books table. While it is quite easy to Link tables in Access, Managing them is entirely a different ballgame. In order to create the relationship you first need two tables. A Lookup field in a table displays the looked-up value. Simply open your Word document, scroll down to the figure and select figure’s caption. You can update one object at a time, or update all of them at once. If we do not, we will read a configuration to obtain the location of the Back-end database and we will About this tutorial: Video duration: 30:29 Part 5: Automatically Re-Link Broken Table Links In this video we will run VBA at application startup to check to see if we still have a good link to our Back-end database. Active Record automatically creates methods to allow an application to read and manipulate data stored within its tables. A window appears when the download is complete. On your Apple TV HD or 4K, go to Settings > Apps. Select Microsoft Excel as the. "The Met Police Press Bureau uses an online provider called MyNewsDesk to. As one PowerPoint newsgroup visitor put it: "I have a presentation in MS PowerPoint 97. Auto update a chart after entering new data with creating a table. You cannot link to queries, forms, reports, macros, or modules. The problem is that new rows are entered into the database every day, and when I update the link in Excel, the rows in the linked table move down accordingly, but the three Excel columns do not. 1 Create Active Record objects can be created from a hash, a block, or have their attributes manually set after creation. Click the Update tab to access the settings. Adding fields across your linked tables. When you need to switch to a different back end location you must do one of the following: 1) Delete all the linked tables. 3, NIC names have changed from names such as eno16777728 to ens192. When you create a relationship, Access will automatically add a sub-datasheet to that table. To do: Just select the table of contents, click Update Table, and choose Update Entire Table in the dialog box that. Here is what I would like to do. possible to update a linked text table. Enable connections between tables to ensure that they share a relationship -- without relational data, you can't auto-fill in Access 2013. Update Query Option when Designing Queries in MS Access 2007 and 2010. When a record in a parent table is deleted, Access deletes all the related records from the child table where the value from the primary key matches the value in the foreign key. Media Molecule calls it DreamSurfing, wherein you can browse featured content. Pivot Table Overview. Amazon SimpleDB automatically manages infrastructure provisioning, hardware and software maintenance, replication and indexing of data items, and performance tuning. net is a forum dedicated to Microsoft Access, if you want to ask any Access related questions or help other members out, please join our community , the registration is free and takes only one minute. How to Convert Numbers to Text in Microsoft Access. In the Data Type tab, choose "Lookup" from the drop-down menu to launch the Lookup Wizard window. When a record in a parent table is deleted, Access deletes all the related records from the child table where the value from the primary key matches the value in the foreign key. TAGs: SQL Server. Linked tables in Access use absolute paths rather than relative paths, so the development environment either has to have the same path as the production environment or a "dynamic-linker" routine can be written in VBA. Excel automatically refresh pivot table You may have a pivot table that links to data in the same workbook or external data, and you may want to refresh pivot table automatically in order to see the most updated data. I'll use Word 2013, but you can use exactly the same method in Word 2010 or Word 2007. Got any Excel/VBA Questions? Free Excel Help. Access can automatically create several types of forms. For example, when you click the Form button on the Create tab, Access places all fields in the selected table on a form. The Where criteria is based on the unique index. Again, click Paste > Paste Special in the Home tab. The type of linkage among those 4 tables is known as  inner join. The database application is created by using Access as the front end (The part of the database application which has the User interface , Data entry forms, Reports etc) as well as the backend (The part of the database application which has the data) of the database application. Word does not automatically update the Table of Contents as you type, but if you make changes to your document and need to update the Table, you can do this quickly by selecting/highlighting your Table of Contents, and then going to the "References" tab on the menu and clicking the "Update Table" button next to "Table of Contents". Adding new tables. How can I create one linked table that automatically updates with new entries in the input data tables?. From Access to Outlook-Add Your Custom Data to the Outlook Calendar Probably the most used Microsoft Office Application is Outlook. This is the simplest way to automatically refresh pivot table. How to auto-refresh pivot table. , but server logistics can sometimes get in the way. Office 2013 updates, August 2018 KB3172506 – Update for Microsoft Office 2013 that fixes an update issue for new meeting locations when using the Rooms button or the Room Finder. I need the linked tables to refresh when Access opens the database. This includes many of the most popular productivity tools, adding. Type 5, and then press Tab. Watch the first video in that series on Pivot Tables & Dashboards. net is a forum dedicated to Microsoft Access, if you want to ask any Access related questions or help other members out, please join our community , the registration is free and takes only one minute. BTW, i connect to database using network drive, here is what i do Tableau - connect to DB using MS Access - Browse - goes to Network drive - open specific Database - bring table (s) in Tableau - create extract and work using that I am confused about where to give path - so server can find and refresh files automatically. Update your operating system and other software. It should be obvious under the Queries tab. I was told that just by opening the database the linked table is automatically updated, but I suspect this is not true. In the Data Type tab, choose "Lookup" from the drop-down menu to launch the Lookup Wizard window. Would anyone be able to advise on how to do this? or if it is possible?. Select Value Field Settings from the pop-up menu. As a systems administrator, you’ve probably noticed that computer objects in Active Directory have a description field that is shown in the default view of the Active Directory users and computers MMC console. Bottom line: Before releasing your document, turn off Track Changes then update the Table of Contents, List of Tables, and List of Figures so that they reflect the current headings, captions, and page numbers. If not already automatically done by Access, drag the Product ID field under the Orders table into the Product ID field under the Products table You have now linked your 4 tables together. How to auto-refresh pivot table. Watch the first video in that series on Pivot Tables & Dashboards. Now create Jet link tables to the corrupt database tables. When prompted, accept the option to install the new release. UPDATE table_name. MS-Access Tips for Serious Users. Summary: in this tutorial, you will learn how to insert new rows into a table using the PostgreSQL INSERT statement. Please do as this: 1. Copy the table from the example workbook, and head back to Microsoft Word. Combine Excel worksheets by matching columns. This table will provide you the details about Auto provisioning status for a role. It is quite simple to link an Access database table or a select type query to an Excel worksheet. To do that, you have go to the menu Data > Connections. TOC styles are set to update automatically, so you can format your TOC 1 paragraph as bold, and that will update the style. Convert Data into a Table After Creating a Pivot Table If you already have a pivot table in your worksheet. A Table of Contents is a simple yet extremely useful feature in any Excel workbook that contains more than a few sheets. MDB (see Split your MDB file into data and application ), and move the files to a different directory, all tables need to be reconnected. com or MongoDB: Open your Microsoft Access database. So using Automatic except Tables will stop Excel from automatically triggering the multiple calculations at each calculation, but will still calculate all dependent formulae except tables. Then change the SortKeyPosition to 1. A long-standing frustration with pivot tables in Microsoft Excel is that any fields that contain numbers must always be manually formatted to add commas and/or remove decimal places. OLAP Cubes in Excel. Store your data in SQL Server and Microsoft Azure SQL to enhance reliability, scalability, robust security, and long-term manageability. So, what are they exactly, what do they do, and what can be done about them? Hidding Access System Tables Access System Tables are read-only. Select Microsoft Excel as the. Is there a script, macro, or command line option I can use to do this? Thanks. See also Wikipedia:Redirect § Targeted and untargeted redirects. The SQL UPDATE Statement. The purpose of this is so that you do not need to store redundant data. Click the Update tab to access the settings. Automatic Mail Merge With VBA and Access November 23, 2015 by Ryan McCormick 18 Comments I once built a program with MS Access that handled a mail merge job of ~1000 letters per week. Would anyone be able to advise on how to do this? or if it is possible?. You can significantly improve the performance of your Access database by maintaining an open connection to the back-end database throughout the time your front-end database is opened. By default, Access creates an ID column that increases in number for each entry. For example, the Northwind database has an Order Details table that includes a ProductID field—a foreign key from the Products table. MDB (see Split your MDB file into data and application ), and move the files to a different directory, all tables need to be reconnected. You can see that the Linked Table Manager now displays the new location of the backend database for each of the linked tables. Are linked tables the best way to use SQL Server? Yes, no, maybe… I've been seeing a lot of talk lately about linked SQL Server tables…ever since I've started this blog I've address it several times, but there have been some changes since my last diatribe that warrant revisiting the issue again. To update linked tables by using Access. Select Microsoft Excel as the. Re: How to automatically update workbook linked to other excel files? I can't seem to go anywhere with this problem. The Edit Links dialog box appears. by Hollie Miller (UK) Microsoft Access is a database management tool. You can let Do Not Disturb While Driving turn on automatically, or add it to Control Center for quick access: Go to Settings > Control Center, then tap Customize Controls. Copy the table from the example workbook, and head back to Microsoft Word. Adding new tables. If you forget to add a new table to your list of linked tables, it will be excluded from your LinkTables routine and may cause you some embarrassment when your new app is rolled out. The Linked Table Manager only lists the tables and the name of the database. The link to to another table in another access database. Updating local table records only if a linked table has changed AccessForums. Type wood, and then press Tab. To save the table, in the title bar of Microsoft Access, click the Save button; To switch the table to Datasheet View, in the Views section of the Design tab of the Ribbon (if necessary, on the Ribbon, click Design or click Home), click the View button. I have a set of linked tables (To Oracle DB) that I need to update via a form. (As mentioned above, opening the linked - front end - table in design view will bring across any design changes, but you don't want to have to do that for lots of tables in turn). The employee has relationship or links to the job or task that he or she performs. Tables can also have foreign keys. Super post - and great domain by the way:-). It should be obvious under the Queries tab. If you want to stay informed of my updates on this topic and other articles, click the "Follow" tab at the top of this article. 16 articles in this collection Written by Robin Burdick. MS Access to SQL Server linked tables using VBA. If the database is updated, all it takes is a couple clicks in InDesign to synchronize with the datasource and all values in your layout update. The linked tables have now been updated. A reader named Julie has a regular chart that is linked to a pivot table. MS Access Forms MS Access Queries MS Access Report MS Access Tables 14 responses on " MS Access - Listing of Database Objects (Tables, Queries, Forms, Reports, …) " Grambo September 5, 2010 at 4:44 am. Receive automatic case status updates by email or text message,. Working with external text files in MS Access. make a new query include both tables tie them together with the product number in any empty field (where the field name goes) type: Balance: [Quantity on hand] - [product shipped].