Trying to populate a field in main form based on value from unbound textbox in datasheet subform (1 Viewer)

[Allyj]

Hi there, novice alert... Please help
I have a question:
Is it possible to retrieve the value of a unbound textbox that resides in the footer of a datasheet in a subform and copy that value to populate a bound field on the parent form?
The unbound textbox is just a basic sum function which totals the rows of line items. I can get it to work with an unbound textbox on the parent form but I would like to save this information as I use it in other calculations. Any suggestions, I suspect its not the right thing to do but is it at all possible with some after update event or something?
Ally

 

[MajP]

Yes you can, but it sounds like your database is not properly designed. There is no reason you should be saving a calculated field in the main table based on values of the child table. Can you provide more detail?

 

[Allyj]

Thanks MajP.. I suspected I am breaking the conventional database rules. I would like to store this amount because I use it for a calculation in another part of my database. Basically I have managed to do all other calculations in the query but I need to pull that total and include it in the sum but tried all sorts of ways with no success... ive just woken up (NZ time here) so when I get to work I will post a liitle more detail. Thanks

 

[Gasman]

I would just have a function to do that?, and call it when required?

 

[MajP]

to store this amount because I use it for a calculation in another part of my database

Instead lets figure out how to use this calculation without storing it. If you can calculate in a subform you can calculate in a query.

 

[Allyj]

Instead lets figure out how to use this calculation without storing it. If you can calculate in a subform you can calculate in a query.

Ok here I go bear with me, I have a table called quotes which works out a new quote for a piece of jewellery based on a historic piece I have made in the past. I store all the relevant information for each piece in an Inventory with each design having its own unique InventoryID, the information relating to each piece is stored in another table called Worklog these two tables are related with a one to one relationship. The Worklog table contains the metal the piece was made in and the weight of that metal, the labour and all the extra information like gemstones etc that were used for that piece and a customerID for whom the piece was made for.

I created a new table called Quotes, I look up the inventoryID for these historic pieces using a combo box on my form that then populates new fields in my QuoteForm and I have written some code which will convert the specific weights of the original piece , recalculate the new weights based on the Option1 and Option2 fields which are combo boxes for the choice of precious metals available.

The next step is I pull in the latest precious metal prices which I update regularly and store in a table called MetalPrices.

In a query I calculate the weight conversion as I mentioned and then the cost of the weight * the option chosen for the new quote.

This happens twice as I have Option1 for the primary metal and Option2 for the secondary metal for example a ring can be made in 18ct gold with a platinum setting and those weights and values are different.

The QuoteForm also has a subform which is a multiple items form which allows the user to choose from a list of extras or add to the list, these extras would be things like gemstones or chains for necklaces of outsourced work like engraving cost etc.. The unbound textbox which totals these extras works fine and I can get all this to work and print a report with the calculated new values. Super happy I got this far

The next problem is that I would like to then (if customer accepts quote) have some button that will open a new entry on my Inventory Table add the weights that were quoted to the worklog and generate a new InventoryID. I would like to then create a Report that then prints out the piece (which have a filepath to an image) the newly created InventoryID The weights and options, size etc and what the customer has paid in deposit,outstanding balance, customers name and contact details etc. This information is printed in a report called Jobsheet and given to the jeweller.

I have managed to pull the information from the Quote Table using a combo box and code that populates the fields

for example: Me.Weight.Value = Me.ActiveControl.Column(2)

This all works well until I want to print my jobsheet. Since the quote report is based on quote table I can calculate all my values together easily.

But, I am struggling to find, pull across the totals from my quote to my Jobsheet Report (for the jeweller) with the newly created InventoryID
I guess all I need from the Quote Table would be the total $, deposit paid and the outstanding balance. As all other fields are populating and working fine. (My Jobsheet is bound to the Inventory and Worklog table.)

It's really been perplexing me.
I hope you understand my explanation?? maybe I am going about this the complete wrong way and you have a better solution.

I know that storing redundant information in tables is not the done thing but it seems so much easier to use those values if they exist in a table rather than unbound controls or query expressions.

I have A LOT TO LEARN, and any advice would be appreciated. Sorry if my replies are slow there might be a timezone difference.

kind regards Allison

 

[MajP]

That is probably too much to digest without seeing a sample database or at a minimum a relationship diagram with all of your table information. How mature and far along is this database? It sounds as if some of the tables might need to get modified. If early this is the time to do it. The question becomes when is a band-aid sufficient and when a major redesign is in order.

 

[Pat Hartman]

The unbound textbox which totals these extras works fine and I can get all this to work and print a report with the calculated new values. Super happy I got this far

If you don't save the additional items, your only permanent record would be the printed quote. That is not a good design. You should use a separate table for extra items so you can keep the details about each. When you copy an old quote, you can include copying the "extra" items or not, whichever makes sense for you. You can even make two buttons. Copy, Copy with Extras.

I would probably use the same table to store quote and order. Just a flag/date value turns a quote into an order. Then you create the invoice or order report from the quote data. This may also solve the problem that is causing you to want to duplicate data.

 

[Allyj]

If you don't save the additional items, your only permanent record would be the printed quote. That is not a good design. You should use a separate table for extra items so you can keep the details about each. When you copy an old quote, you can include copying the "extra" items or not, whichever makes sense for you. You can even make two buttons. Copy, Copy with Extras.

I would probably use the same table to store quote and order. Just a flag/date value turns a quote into an order. Then you create the invoice or order report from the quote data. This may also solve the problem that is causing you to want to duplicate data.

Thanks Pat, yes the table is called quote but I am intending on it being for quotes and orders. The Inventory table/worklog is updated once the piece is made and these unique IDs are used and put on photos in an album so if a customer wants that design we can refer back to it with all its details. Sorry the extras does have a table of its own which stores all the information for one QuoteID so it's not lost. Really I wanted to generate the job sheet to make it easier to go back to that record. Our old system we manually coded items and there were too many mistakes like two different photographs with the same ID#. Thank you for taking an interest.

 

[Pat Hartman]

OK, when you print the quote or invoice, you use a report and probably a subreport so you should have all the numbers you need. You simply use the same subtotals you used on the form.

 

[Allyj]

That is probably too much to digest without seeing a sample database or at a minimum a relationship diagram with all of your table information. How mature and far along is this database? It sounds as if some of the tables might need to get modified. If early this is the time to do it. The question becomes when is a band-aid sufficient and when a major redesign is in order.

Hi MajP,
I hear what you are saying and I apologise for the lengthy thread.
I am struggling to work out the relationships.
I have tried different ways of going about this and this seemed to most be the most straightforward. I'd be happy for you to take a look, although slightly wincing about the band aid
Thank you I will upload a sample..

 

[Allyj]

OK, when you print the quote or invoice, you use a report and probably a subreport so you should have all the numbers you need. You simply use the same subtotals you used on the form.

Yes Pat, this part works fine (the customer Quote report)
I would like to then start the Jobsheet report for the jeweller. In ideal world, this would assign a new InventoryID put the estimated weights of the metals (from the quote) and the other job details. Also to avoid the step of finding back the quote to see what outstanding balance is there, I thought it might be easy to pull those totals from the quote to print out for the jeweller.So they can call the customer on completion and all the relevant info is on hand. Once the piece is made all they have to do is photograph it save the filename as that InventoryID their Jobsheet. Then when the time comes to do data entry, we could just refer back to the InventoryID adjust the weights if necessary and put the image filepath with it.
Hope this makes sense.
The stumbling block to grab these totals, especially the unbound sum of the extras list, which is why I thought I could break conventional rules and store that total somehow in a field in quotes so I can easily find it for my Jobsheet.

 

[Pat Hartman]

I'm having trouble figuring out why you can't calculate the totals from the Quote tables as you do for the Quote report. If you can calculate the totals on some form, you can calculate the same totals on some report. If the totals are from a subreport, you can create a query that sums the data and join to that query or you can join to the child table and sum in the main query. Just don't sum the figures from the parent table. Use group by for those values or you'll duplicate them.

 

[Allyj]

I will put some dummy customer information in my database and attached it for you guys to have a look shortly.

 

[Pat Hartman]

1. Why do you have the relationship between worklog and inventory defined as 1-1? That doesn't make sense.

2. Also doesn't make sense for Finding Services to be 1-1 with worklog. Plus, there is invalid data in the ExtraID field of worklog so the relationship cannot be defined.

3. The MetalPrices is a matrix and it should not be. Each metal should be a separate row with the price on a date. Tall and thin, NOT short and fat is how tables work. With the matrix structure, you can't pick up the price with a join. You need code because you have

4. I know the Attachment data type is cool but depending on how many images or documents you need to store, the BE could get very large, very fast. Holding a text file name is a much more efficient option.

5. When you have column names that have suffixes, you probably have a repeating group and that needs to be a separate table. Once you have more than one of something, you have many. So storing two values for metal and the ancillary data that goes with it is wrong. Each metal type and its associated attributes belongs in a row in a separate table. you are not saving ANY work by storing them as multiple columns in the main table. What happens if you end up with some piece that uses three different metals? Do you refuse to make the piece because your database can't handle it? or do you design the database for flexibility?

6. I don't understand what the point of the inventory table is. I thought these were bespoke pieces.

7. Why is worklog related to Inventory and not Quote? Doesn't everything start with a quote? Do stock items also start with a quote?

8. Why is FindingsServices related to worklog instead of Quote? Or Inventory?

9. Why are there so many missing relationships?

10. Do you only buy findings for specific quotes? How does that jibe with inventory?


And finally, you can't just drop a database filled with objects that we do not know how to use and say give it a whirl. Maybe you should hire testers. You might want to at least build a menu or switchboard to give the app some structure.

 

[Allyj]

1. Why do you have the relationship between worklog and inventory defined as 1-1? That doesn't make sense.

2. Also doesn't make sense for Finding Services to be 1-1 with worklog. Plus, there is invalid data in the ExtraID field of worklog so the relationship cannot be defined.

3. The MetalPrices is a matrix and it should not be. Each metal should be a separate row with the price on a date. Tall and thin, NOT short and fat is how tables work. With the matrix structure, you can't pick up the price with a join. You need code because you have

4. I know the Attachment data type is cool but depending on how many images or documents you need to store, the BE could get very large, very fast. Holding a text file name is a much more efficient option.

5. When you have column names that have suffixes, you probably have a repeating group and that needs to be a separate table. Once you have more than one of something, you have many. So storing two values for metal and the ancillary data that goes with it is wrong. Each metal type and its associated attributes belongs in a row in a separate table. you are not saving ANY work by storing them as multiple columns in the main table. What happens if you end up with some piece that uses three different metals? Do you refuse to make the piece because your database can't handle it? or do you design the database for flexibility?

6. I don't understand what the point of the inventory table is. I thought these were bespoke pieces.

7. Why is worklog related to Inventory and not Quote? Doesn't everything start with a quote? Do stock items also start with a quote?

8. Why is FindingsServices related to worklog instead of Quote? Or Inventory?

9. Why are there so many missing relationships?

10. Do you only buy findings for specific quotes? How does that jibe with inventory?


And finally, you can't just drop a database filled with objects that we do not know how to use and say give it a whirl. Maybe you should hire testers. You might want to at least build a menu or switchboard to give the app some structure.

You indeed have given me a lot to think about! I really do appreciate you taking the time to have a look at my project.
By the way The customer information adresses exist but they are dummy names and emails generated from something similare to what you suggested.

1. Why do you have the relationship between worklog and inventory defined as 1-1? That doesn't make sense.

Ok, my Inventory table was getting really big so I thought I would split it in two tables. By the way Inventory its probably a really bad name for this table as its more a catalogue I guess.

2. Also doesn't make sense for Finding Services to be 1-1 with worklog. Plus, there is invalid data in the ExtraID field of worklog so the relationship cannot be defined.

This is probaby a mistake of me fiddling with the relationships - sorry

3. The MetalPrices is a matrix and it should not be. Each metal should be a separate row with the price on a date. Tall and thin, NOT short and fat is how tables work. With the matrix structure, you can't pick up the price with a join. You need code because you have

This is the format I get the prices from my bullion merchant but I will look into this.

4. I know the Attachment data type is cool but depending on how many images or documents you need to store, the BE could get very large, very fast. Holding a text file name is a much more efficient option.

Good point and in actual fact i don't think I would use it that often so it could change.

5. When you have column names that have suffixes, you probably have a repeating group and that needs to be a separate table. Once you have more than one of something, you have many. So storing two values for metal and the ancillary data that goes with it is wrong. Each metal type and its associated attributes belongs in a row in a separate table. you are not saving ANY work by storing them as multiple columns in the main table.
Ok will look into that

What happens if you end up with some piece that uses three different metals? Do you refuse to make the piece because your database can't handle it? or do you design the database for flexibility?

I did think of this and decided two options covers 99% of my jobs. In 20 years, I only ever made one piece with a three metal combination and they were all gold, the values of coloured alloys are so similar it's almost neglegible. Any other combination wouldn't happen together for manufacturing reasons. In this case,i would just write in internal notes or manually work out a quote for this.

6. I don't understand what the point of the inventory table is. I thought these were bespoke pieces.

Well, its a mix of stock we make and have made in the past and custom made pieces too. Its basically a catalogue of everything that we've made or taken record of.

7. Why is worklog related to Inventory and not Quote? Doesn't everything start with a quote? Do stock items also start with a quote?

No stock items are made and their prices are worked out in retrospect. I guess Inventory is more relevant to stock items but we also quote stock items to be made in a metal alloy of customer choice.

8. Why is FindingsServices related to worklog instead of Quote? Or Inventory?
I did it this way as worklog is all the necessary information for the making of a piece

9. Why are there so many missing relationships?
Because I am a novice at this and still working it out

10. Do you only buy findings for specific quotes? How does that jibe with inventory?
No a lot of this information has come from an old system previously everything was hand written in a notes section, I am trying to find a better way to deal with it as a lot of findings are used on a regular basis and everytime we have to look back on invoices to get the prices. And Yes we do only buy what we need as we only hold a limited stock.

And finally, you can't just drop a database filled with objects that we do not know how to use and say give it a whirl. Maybe you should hire testers. You might want to at least build a menu or switchboard to give the app some structure.
OK sorry, I think I will go lick my wounds now.

To put this all in context, we are a small jewellery business in a small town in a small country with a small customer base. Our country has had its doors closed to the world for two years and we're hurting. There is a common misconception that jewellers are dripping in gold and diamonds but I am yet to meet one (at least In NZ that is!) I am trying to do this on my own with no prior experience. I went to our library and they had one book on access which was from a users perspective not designing one. I thought I would try this winter to put something together that might be a bit easier to use and save us time in the long run.
I do intend on putting a switch board. But after reading your advice its seems i should go back to the drawing board and start again. After all in my trade when i am given a piece of useless junk to fix, we say in the workshop 'you can polish a turd, but its still a turd.'

Thanks I'll go do some more homework
Ally

 

[Pat Hartman]

I love your attitude Now that I understand the business better, I can offer solutions rather than questions But I'm busy at the moment. If you don't see something by tomorrow, PM me

 

[Pat Hartman]

1. There are reasons for creating 1-1 relationships but this isn't one. I think the table has more columns than it needs to because some of the data should be in child tables. I know you said you almost never have more than 2 metals but that is not a reason to not normalize the data. When you normalize, you can now add another metal if you need to without doing anything else. I would create a second table for stones. I don't know if it is important to you but once you organize the data this way, you can analyze it should you choose to. You can easily find the biggest seller or the supplier with the best prices or the favorite stones. I would also add a table for labor.
2. Once you add the additional child tables, you can embed the finding information in the metals table.
3. It will be pretty easy to normalize the price data as the new price sheet comes in. Is the price standard across all suppliers? If not, you need to add supplier to the table.
4. Whenever you have a picture, you would save it in the images folder and add the file name to a text field. Then you can display the photo on the quote and/or on the inventory form
5.
6,7,8. I'm still thinking about what would work best. we would need more information to decide. At the moment worklog, Inventory, and quote are still muddled in my mind. Do you need to track inventory or sales of inventory items with this application? It feels like it makes more sense to have deposit paid and balance in the quotes table than in the worklog/inventory table. If you need to track actual sales, then you need a separate table so you can sell from inventory or from quote. OR, if all quotes get added to inventory, then inventory could act as the sales table but that only works if you have a separate row for each item (as my head spins). You can't have 5 in stock and sell 1 and use the inventory table to handle that. Also, customers is linked to Quote, but do you track customers who buy from inventory? Maybe quote should be the build information whether or not the item is for inventory or bespoke. It still needs a link to customers for the bespoke items but not for the others.

I'm just talking so you can clarify your thoughts also and think about what you want out of the application. Maybe, you could think about what reports or stats you want from the app or what pieces of information you are now searching paper records to find
9.
10. You might need to add findings as inventory items. Then you would use a junction table to connect the finding to a quote or an inventory item. This makes me lean toward requiring a quote to "define" each inventory item. Then inventory is just inventory info. I don't know how you handle invoicing but I assume you don't need it in this app. However managing inventory by updating the quantity field leaves a lot to be desired. Mostly because it leaves no audit trail. This is probably not a big problem for you but in most applications, I would use a transaction table to add or subtract from inventory so you have a better shot at keeping it clean.

I'm trying to not over-engineer a solution. A small company with competent people doing the data entry can get away with a pretty bad schema (as long has you have note fields in most tables) but if I can help you to do a little more engineering, you'll be better off in the long run and get a lot more out of your work product. I think analyzing the outputs that you require and then what you would like will help.

 

[Allyj]

Pat, Firstly, I very much appreciate the time you are taking to help me suss this out. It's very kind of you!

I think I will rename the inventory to "Catalogue" as regardless of whether it was a piece we made for our shop stock or a special commission, everything we make can be made in a choice of whatever a customer desires.

Is the price standard across all suppliers? If not, you need to add a supplier to the table?
- No I will add a supplier table

Whenever you have a picture, you would save it in the images folder and add the file name to a text field. Then you can display the photo on the quote and/or on the inventory form
Yes, I will name these according to the ID code from the inventory/catalogue

You might need to add findings as inventory items. Then you would use a junction table to connect the finding to a quote or an inventory item. This makes me lean toward requiring a quote to "define" each inventory item. Then inventory is just inventory info. I don't know how you handle invoicing but I assume you don't need it in this app. However managing inventory by updating the quantity field leaves a lot to be desired. Mostly because it leaves no audit trail. This is probably not a big problem for you but in most applications, I would use a transaction table to add or subtract from inventory so you have a better shot at keeping it clean.

Little unsure , do you mean add findings to the inventory(catalogue) table itself? I had consider this approach but decided it might be easier as Table/ list on its own that could include its own categories, Stones, chains, findings, services form contactors etc being as these are all outsourced extras to the work we do in-house. It would relate to the supplier list. Maybe this is what you just explained....Junction table sounds the way to go.
I'm just talking so you can clarify your thoughts also and think about what you want out of the application.
Originally we sorted all information together under three main categories:
CM = custom-made pieces
JB = STOCK Jewellery Items made from scratch (no cast patterns so more like one-offs)
YS= Stock items that have a cast pattern number associated with them and these are pieces that are cast for reproduction (these items are made and stocked in store on a regular basis)

We have photo albums of all jewellery we make CM, JB and YS. The photographs are coded for example CM807 or YS601.
The scenario is, a customer may come into the shop and not find something off the shelf. They start looking in our photo album, they see something they like and want a price. It takes a long time to work this out on the fly, especially if they start wanting different options of metal etc.. If I could automate this based on the information recorded for that piece it would be a great advantage.

the information we need to create a new quote is
-the weight
-the original metal it was made in
- the labour times
- Information on the extra items, suppliers etc

The hardest part for me was also how to store and relate all this information. I want to keep the original values from this Catalogue intact but I don't see sense in having a bunch of quotes which may or may not be actually made stored in the same place as your historic information from this catalogue.

I was thinking a one-to-many relationship could exist. One Catalogue item 'CM807' could have many quotes made for several different customers.

Quotes and Orders could be stored together as you would not have an order exist without a quote first as values of precious metals are constantly changing? I was thinking that a simple yes/no field could exist to turn an accepted quote into an order.

But once it becomes an order it then has to be made.
The hardest part for me was how to then create my Jobsheet for the workshop as I think ideally, it should now become its own new Catalogue item. Because this report was based on the new Catalogue entry and not the quote I could not pull the relevant information about customer details and outstanding balance as these were based on a report based on the Quote.

In short, I would like to figure out how my new Order/Quote relates to my new entry into the Catalogue.

My head is spinning too!

The other problem (maybe it is not a problem) I need to be able to create a quote from scratch from something that does not exist in our Catalogue by entering my estimated weights and metals but maybe this is just a different form that is used a variation of the Quotes done from the catalogue. It would probably be good to consider this now instead of crossing that bridge when I come to it. Which is the usual reason I get into difficulties, bad planning on my part.

I think analyzing the outputs that you require and then what you would like will help.

I think this database would mostly serve these purposes:

• to generate customer orders and quotes
• customer history if a customer piece is lost or stolen we can recreate it.
• For valuation/ appraisal reports for customers to have for insurance purposes
• For the workshop to lookup stock items from time to time to see their labour times and weights etc

Stock Inventory is mostly managed from our online store but our database would be useful for our shop stock take only in as much as a Printed out list filtered into types like earrings, rings, that are YS and JB that we could tick off. I don't really think we would use it for managing stock-in and stock-out quantities as such. Like you say a small company we usually just have a manual written list of pieces we sell and look in the cabinets to see where the gaps are to replenish this once a week. It's manageable on this small scale.

I will create some basic tables and maybe work them out on paper first.

Thanks again for your kindness,
I would like to return the favour in some way,

Regards
Allison

 

[Pat Hartman]

I have 50 years of experience in designing applications but even I work through the table design by talking with a subject matter expert. You are the subject matter expert but you are not the database expert so the initial separation of data into tables has to be very hard for you. As I help you to work through this, I am trying to avoid having tables that have the same data and that are used for slightly different purposes. I like Catalog as the descriptive part of what you sell. But, I'm thinking that would be very high level. Type of item (Ring, Brooch, Earrings, etc.) Sales price maybe dimensions so people know that the earrings are 10 mm by 8 mm. Link to the photo. An indicator as to whether this is a standard item or custom. This helps you to select items that need to get uploaded to the website vs items that you might show to a customer in the store. The "quote" which might be the "build definition" of a piece would hold the details in it's child tables. The metal, the stones, the gram weight of each part, the labor hours, the findings. This allows you to use this table regardless of whether this is a custom piece or one that you build and include in your catalog. I would include queries that calculate percentages of the total cost. Metal represents 30% of the cost, stones represent 20%, labor, x, findings y, etc. That way, it would be easier for you to calculate substitutions on the fly or at least quickly. The app could do this for you.

Forms generally show a single table at a time. Details are shown in subforms so the quote would be a more complex form because it will have several subforms to show the repeating items such as metal, stones, labor, findings.

You have tables that contain rates. The hourly rates for different categories of labor if you separate them. If you don't separate them now, you might want to consider it. Then you have the rates for findings and metals and stones. All of these are date sensitive. When you add an item to the metal section for example, it picks up the current price of 18K gold or whatever and saves it with the quote. That allows you to know what you paid for something. However, you can always, using a query, pick up the current price so something that cost $500 last year, might cost $595 this year. Prices are stored by Vendor, product, startdate, enddate. Adding both a start date and an end date makes using the table in a query much easier. In the form I use to change the price of an item, I add an end date and then in the afterUpdate event of the form, insert a new record with the start date following the end date and a new end date of 12/31/2099 or whatever you want for a future date.

Then you need an inventory table which is a child of Catalog and it is updated to keep the quantity on hand current.

A customer order table would be used to initiate a custom quote. This is where you would have the total price and deposit information. Money beyond that probably should not be incorporated as part of this build since I'm assuming you have a system to handle that already.

The quote table could be used for appraisals. Just another type to add to standard and custom. Think about the data you need to do the appraisal. Perhaps it will cause us to decide to move some attributes from the Catalog to the "quote".

There is a junction table between customer and quote that connects the customer to custom items and appraisals.

The catalog table would have a FK to the quote table. So if a customer purchased something from the catalog, you could use that junction table to connect them all the way to the catalog if necessary.

I'm thinking of findings as castings or forgings that serve as earring backs or the base of a ring or brooch or pendant or bracelet. But you mentioned chains fall into this. While you wouldn't sell a casting all by itself, would you sell a chain? I'm trying to figure out if a finding is actually an item you sell. Sometimes, half chains are welded to a centerpiece and become a permanent part of a necklace but pendants have loops that a simple chain slides through and the chain could be used with other pendants.

Starting on paper first is a good idea. Sometimes, I actually use Excel or Access for this . One sheet per table. That means I have less typing/rewriting to do as I move things around. When I work in an office with team mates, we often use post it notes on a wall. Those are really easy to move around as we decide which attribute goes in which table. When I use Access, I have a table of table names and a table of field names. Then I have a junction table that connects fields to tables.