4 Replies Latest reply on Jun 12, 2012 5:55 AM by philmodjunk

    Access developer needs FM help



      Access developer needs FM help


       I'm helping a friend that is brand new to FM and databases.  I know MS Access, but don't know how to use my Access knowledge within FM.  I want to make a query (listing?) using three separate tables, but can't find anything within FM that will do that so I can use this listing with a form and with a report too.  Does anybody understand and can help me understand how to use FM, so I can help my friend with FM?


        • 1. Re: Access developer needs FM help

          You'll need to tell us more about your three separate tables and any relaitonship you may have defined in Manage | Database | relationships.

          Basic relational database design is much the same between the two systems, but FileMaker doesn't use SQL to query the data and set up a "record set" of data. Instead, you, or a script performs a "find" that produces a "found set" of matching records. While there are many similarities, there are also many differences.

          One key difference is that it's easy to use separate tables for different categories of the same basic data in Access and use a query to pull the data together in a single table for reporting purposes. In FileMaker, you would put all the different categories in one single table--which is not a bad design to use in Access either.


          You have contact (Name, address, etc) for "vendors" and "customers". In Access you can get away with a less than optimum design by using one table for Customer contacts and another for Vendor contacts and then use SQL to pull it all together for a report that lists contact data from both tables.

          In FileMaker, you'll have a lot of trouble with that approach. Putting all the contact data in one table with a "type" field that identifies a given record as "Vendor", "Customer" or both makes for a much better design approach.

          But that may not be what you are dealing with here, so please describe your tables and relationships.

          • 2. Re: Access developer needs FM help

            First, I will add a little about my overall background.  I do know how to normalize my files, and have helped my friend by taking all of the data she is dealing with (currently in a bunch of Excel spreadsheets), identified all the data elements, designed the relational database to the third normal form, and defined all therelationships between the tables in FM.

            Based on your example of spliting what should be one table into two, rather than having an identifying field within one table to define the record as a "vendor" or "customer" record, I can assure you I have not done anything like that in this design.  Rather than getting into this specific database, which would also require a lot of education as to what the fields are, I feel it best to stick with simple examples of how these tables are related.

            So, my three simple tables will be an order master table, order detail table, and a product table. (We will ignore the customer master table.)

            tblOrderMaster fields:  OrderID (autonumber), CustomerName, Address, City, State, Zip, DateOrdered
            tblOrderDetail fields:  OrderDetailID (autonumber), OrderID, ProductID, QuantityOrdered, ItemPrice
            tblProductMaster fields: ProductID (autonumber), ProductName, ItemPrice, QuantityOnHand

            Now, what I need is a way to (using Access language as I really don't know the correct FM terms yet) "query" these three tables at one time in order to see a listing of the orders that will be shipped to zip "92010," or any other zip I may want in the future.  And then, how to print a report of the same information but formatted differently because the paper is wider than the screen.

            Also, I need to know how to set up the forms in order to input a new record in tblOrderMaster and its associated records in tblOrderDetail and reference the products from tblProductMaster to be added to the tblOrderDetail. 

            Thanks for your help.

            • 3. Re: Access developer needs FM help

              If you want to list the 5 orders that are going to 92010 then go to a layout based on OrderMaster and search for '92010'.

              If you want to list the 50 order lines that are going to 92010 then go to a layout based on OrderDetail and search for '92010' in the related OrderMaster Zip Code field.

              Printing from the first will give a list of 5 order headers.

              Sorting the second by OrderID (and with a sub-summary break by OrderID) will give a listing of 5 orders each with their share of 50 order lines..

              • 4. Re: Access developer needs FM help

                You can download and examine this invoicing demo file: http://fmforums.com/forum/showpost.php?post/309136/

                There is also an invoicing starter solution you can examine. It uses much the same principles as the above demo but in a more complex design.