#Name? Unknown Field

[williamson1979]

I've resolved the combobox issue in the subform, unsure what was wrong. I deleted and re did works fine now.

Where I really need advice is how to set the relationships from my "Primary" table to all of the subform tables so that all entries use the "Primary" tables ID. This part I still cant get my mind around. As you know all that days data is used throughout the entire report for final calculations. Example: tblProduction (subform) has a ID of 1. Click on the + and tblPlantReport (Primary) has a ID of new as if it doesn't record any entries.


Also I don't understand yet how to get " Some Sum" from subform to the "Primary" form. Example: Subform total tons sold "sum" equals 1000. I need the 1000 to go to "Primary" txtbox Paid Tons so it can be added to "waste" to equal "Total Tons Produced" which is used to calculate "Production Rate". Later in the process "Total Tons Produced" is used be multiple "subforms" to calculate percentages.

I know you mentioned setting master and child in form design but honestly I'm unsure of what fields to link.

[williamson1979]

Hello,
I'm still having a few issues. Hopefully you can look at the uploaded file. I'll list what I notice below. Thanks

tblAsphalt2... All the tables show tblPlantReport with its data except this table and it shows tblPlantReport as if no data was entered.

Also rptPlantReport shows no data. So I've made a error or lack the understanding to use multiple tables in one report.

Since I have broken the db down into shorter tables I may have made errors on setting up the relationships if you could look at that.

I have a few more tables to add but I'd like to just get this part correct before going further. Afterwards I'll get back to trying to get the aggregate subform added and working.

[twinnyfo]

Still totally clueless on what you are trying to do with this DB.

As I've told others on this forum, you can't take a DB that doesn't work, change everything, not knowing what you are changing or why, and then think that it's going to work. Then, asking someone else to try to figure out that mess is impossible.

I stilll think you are trying to fix everything at one time. Take things one step at a time and figure out everything one piece at a time. The slowness with which you approach this project will save you time int he long run.

I can't help you on "everything " because I am more lost with your last DB than I was before.

You need to determine specific issues that you need to fix and understand each step as you go along.

I recognize that learning via forum can be very challenging, but even more so when you don't slow down.

Please, help me out and explain what you are trying to do. Otherwise, I can't offer any useful advice.

[williamson1979]

I’m working on the relationships. All I need to know if I have set those correctly. TblPlantReport is the primary. I’ve set relationships to the related tables directly. The secondary tables I created relationships to the tables that store information they use.

So I just need to know if I have the relationships set correctly because tblAsphalt2 when I click the plus in the table it does not show a record for tblPlantReport but the rest do.

Also my report rptPlantReport shows no data when viewed. So I think I’ve made errors on my relationships.

[twinnyfo]

This is exactly where I am clueless. I have no idea what you are trying to present in your DB nor how you are trying to present it.

I am sure you've made errors in your relationships, as well as potential other structural issues. But, what those are, I am at a loss to determine what these are because everything is jumbled together and there is no clear intent of what you want to capture in your database.

I am not trying to be mean or harsh. But this forum is not for designing and building someone else's project. Break your problem down into smaller parts of what you want to do and work piece by piece. I can't tell you what those pieces are, because it's not my project.

I am glad to take any of those pieces of your project, no matter how small, and work toward a solution. But, the more modifications you've made to this, the more confused I have become on what you are trying to accomplish. And, "confused" is truly the best word I can use to describe what I see.

You will also notice that our discussions have had nothing to do with solving your original problem. As I addressed the specifics of your original problem, instead of working that one issue, you've tried to restructure everything collectively--before you understood the concepts necessary for solving your initial problem.

Again, not trying to be mean or harsh, but this is not a productive method of problem solving. I hope you can appreciate my confusion.

[williamson1979]

Below is my current issue and a brief description of tables.

tblAsphalt2 does not update field FKAsphalt2 in table tblPlantReport automatically.. .

I have the qry/rpt semi working now. But I have to add the ID to field FKAsphalt2 manually so something is wrong. I have it setup the same as all the tables directly linked to tblPlantReport. Maybe this is the reason>>> tblAsphalt1 and tblAsphalt2 share tblAsphaltCorr & tblAsphatSpecif icGr and they are accessed with vba. Ive listed below what each table is for. From everything I've be told by you and others long tables are bad so Ive broken the tables down as I would file document folders in a cabinet.

tblPlantReport is how I joined all the product/process tables together in relationships. Basically as a container.

tblProduction is a summary of production time and rate of production. This could be expanded to be more specific.

tblEnviromental is weather conditions and temperatures. tblEnviromental Cond stores weather conditions. Minor tweaks possible.

tblFuel is a summary of the fuel used during production. This could be expanded to be more specific.

tblAsphalt1 and tblAsphalt2 is for calculating how much was used. tblAsphalt1Char t and tblAsphalt2Char t converts inches measurement to gallons. tblAsphaltSpGra vity and tblAsphaltCorr are used to convert the gallons to gallons @ 60 F. tblAsphaltGrade stores different grades of the product. Expansion not likely.

tblLime is a summary of this product used in production. Minor tweaking possible.

--------------------------------------------------------------
Every table with the exception of tblEnviromental are dependent on tblProduction for the field txtTotalTon to calculate each products percent of the total.

[twinnyfo]

I guess I am not being clear. What one thing do you want us to address?

[williamson1979]

As prior post I want to address why tblAsphalt2 does not update its ID in tblPlantReport. FKAsphalt2

The other was simply a summary of what things were because you said you needed more info on the structure or why I had tables a certain way.

[twinnyfo]

1. Don't use the actual SQL string as the Record Source for Form. There are no problems with doing this, but it is much easier to create a Query, save it and use the query itself as a Record Source.

2. If you look at your SQL string in Design view, you will notice that tblAsphalt2 has a relationship to AsphaltFK1. I don't think this is what you want.

3. Why two tables for Asphalt which contain identical information? Again, this is exactly the same principle that we've talked about with your very first thread: One table, multiple records.

There is way more I can talk about this design, but the above will get you working with your Asphalt. But again, you've made wholesale redesigns without knowing the direction you needed to go.

[williamson1979]

SQL statement... So access must just generate that automatically. So I remove the source from the form followed by adding my query with all the data as the record source. Sound correct?

Far as the redesign... from our previous communication it seemed to me that I was incorrect by having the long table. So it seemed only logical to break up the different materials and processes into individual tables.

Far as two asphalt tables... I guess I have no reason why other than it seemed cleaner.

If I have understood about the record source correctly could you give me insight on the design you mentioned please?

[twinnyfo]

Correction:

Far as the redesign... from our previous communication it seemed to me that I was incorrect by having the long table. So it seemed only logical to break up the different materials and processes into individual tables.

Just the opposite. You have many tables because tall tables are better than wide tables--although a wide table is not "necessarily" bad--it just depends on what data you are keeping.

=============== ==========

Problem (and this is neither nit-picking nor eye-poking): I think you have a very general misunderstandin g or lack of understanding about basic DB prinicples of table design and structure. We've all been there, so try not to be offended. A corollary of that is that folks often try to start building their forms and reports and output products before they have figured out how things are to be designed first. Then to fix the form or report that doesn't work because things are broken, they try to change something else which breaks something else, and now something else which used to work (by some miracle) now no longer works and they try to rearrange something else (or everything else) and turn the crank and are amazed that now, nothing works. Again: we've all been there at some point--especially if we are self-taught.

So, let me talk broad strokes, big picture, 30,000-foot view and describe what you ought to be thinking about for your DB.

First, what are you really keeping in your DB? A series of production runs, yes? So, your [highlight5]MAIN TABLE[/highlight], upon which pretty much everything else (somehow) should be related is tblProduction.

That table should have the basic information about that particular production run: date, operator, start time, stop time, etc. For the most part, I think you have this.

What you don't have is tables that are properly related to that main table. I will use the Aggregate table from one of your earlier threads, because "I thought" we had gone over this and "I thought" you understood the principle and "I thought" you would have gradually implemented that same principle into the rest of your DB design. However, I find it incredibly odd that the one thing that "I thought" we had working is conspicuously absent from the current DB.

SO, using the principle of the Aggregate, you have up to 10 different types of aggregate (with Percent, used and recieved for each). Rather than using 30 additional fields for your production table--which was your initial design--we broke all the aggregate out into a separate table of only a few fields: ProdID, AggType (because we made a spearate table for that) and then Percent, Used and Received. Notice how the ProdID was the foreign key in the table.

So, use that as your paradigm for your Lime table--just as an example. Let's use two scenarios:
1) You always use lime in every production. You only add it once for any production (I still don't know what the Starting, Ending, Used, Received, Percent means, but it is immaterial, because it should matter at this point). In this scenario, you "could" add all these fields to tblProduction. Remember, this is jsut a scenario.

2)You may (or may not) add lime to your production OR you may add lime several times during a production run OR you may add it every time, but only once. In this scenario (which, by the way, includes the first scenario), it is best to have a separate table for Lime, simply because it gives you greater flexibility in your Lime management (and if you had "Lime reports" you could do essentially all that from one table).

Now, if you are going to have a lime table, based upon the paradigm of the Aggreaget table--which worked--how would you go about designing that table and its appropriate relationship to tblProduction?

ProdID, LimeBlah1, LaimeBlah2, LimeBlah3, etc.

Do you see how ProdID is always the FK in all the tables directly descended from your production activity? In the end, tblProduction (when you view all your relationships) begins to looks like the center of a spider web, with all the other tables coming off that one table, many of which are related via ProdID.

This is big picture stuff, and this is how you need to start thinking about your DB.

But don't just take this advice and change every table all at once. You need to understand these principles first, then once the table structure is built for one aspect of your design, now try to create a form that will work with this design. As I mentioned to you earlier, a Tab Control is a good design for this: You've done that. BUT, each Page on that tabl control should have sub-form specific to each particular aspect of the production. One subform will be about Lime, one about Aggregate, etc.

ONE


STEP


AT


A


TIME.

[williamson1979]

So lime is not always used so it has its own table. Also asphalt1 and asphalt2 fall in line with lime because both asphalt tanks may or may not be used the same production run.

Far as FK always being productionID that seems a quick fix. But when I tried using ID I could not force set update in the relationships so I made fields. So while it might be incorrect that was why I did that.

Starting, Ending, Received or similar verbiage is just on hand inventory to start production and after production. Received is just what was added during production.

[williamson1979]

Before I get off topic. So remove tblPlantReport, FK related tables to tblProduction. If a material is not always included such as lime it has its on table. Fields from tblProduction will go directly to my form. Fields from related tables insert into the tabs as sub forms.

This what I need to work on currently to fix what I already have correct?

[twinnyfo]

Far as FK always being productionID that seems a quick fix. But when I tried using ID I could not force set update in the relationships so I made fields. So while it might be incorrect that was why I did that.

I have no idea what you are talking about here.

Plus, creating a well-designed, fully normalized database is never a quick fix. It is proper design.

This what I need to work on currently to fix what I already have correct?

My assessment is that very little is correct with your current DB.

AND, you're talking about asphalt, starting, ending and receiving again. One step at a time.

Do you understand that you must understand what you are doing before you can move on to anything else?

[williamson1979]

I understood that my relationships need to be created from tblProduction. So if that is the case then I need to remove tblPlantReport entirely.

Once that has happened and my table relationships are created from tblProduction’s PK to the related tables I would then create subforms and insert into the tabs on my form.

I only mentioned starting and ending because you had said you had no idea what they were for so no question or conversation there just simply answering what I took as a question. Maybe I over think statements but if you aren’t sure what something is I try to at least briefly state what it is in the case it matters to structure.

So if I do the above steps for now would that have it falling back in-line far as being correct?