#Name? Unknown Field

[williamson1979]

Do you see how ProdID is always the FK in all the tables directly descended from your production activity?

This is corrected now.

------------------------------------------------------------------
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.

This is corrected now and tblAsphalt2 is updating automatically.
-------------------------------------------------------------------
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.

I have not switched the record source yet, before doing so I'm curious if my data will still be saved in the tables as well.
--------------------------------------------------------------------
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.

I will do work on this tomorrow, very simple layout I use so should not take long. But this brings me back to you saying use query as a record source, so every subform will use a individual qry or can I use my qry that has every table/every field for each subform?
--------------------------------------------------------------------
Just curious here, not requesting details for now. I never could recreate your aggregate subform. Did you use a query for its source to filter it to the forms date?


Thanks, This is a big learning curve. I'm semi getting it I do believe but without tech knowledge/terminology its sometimes difficult to get what I'm asking across in a way it makes sense.

[twinnyfo]

Lots and lots of stuff.

One step at a time. Don't go changing everything right now.

OK - if you've never been able to get the Aggregate to work then this thread is moot.

Get that working first. But, I am absolutely clueless as to how it does not work, as I provided a working sample DB.

One step at a time.

[williamson1979]

When I put the sum function it just made the waiting icon for Windows circle. In the footer I made a unbound box, added sum() with the used and received fields. That’s why I ask if you used query because I used table. But that was just a yes or no. Not looking for great details here now.

My main concern is table structure. You said that was wrong. I corrected what you listed yesterday with the exception of subforms. I’ll do that today.

[twinnyfo]

Again - look at the samble DB I created and posted for your Aggregate. If you had, you would see that it is a query--which is required, because we need values found in the Production Table.

If you can't get the aggregate to work yet, and don't understand what we are doing with that aspect of your DB, I wouldn't go changing other things.

Do you understand that I want to help you understand what you are doing? I want you to be able to see what is going on so that you can apply those same principles to other aspects of your DB.

[williamson1979]

I thought it must have been a query. I just don’t have the file on this pc. So I think with that I can recreate it. I had everything else with it looking correct. I did look at it when you sent it. I tried to recreate it myself rather than just use yours so I actually know how it works.

But from your previous post I think my tables and relationships need to be right before adding that into the dB. So I want to focus on that. Wouldn’t you agree?

So I’ve put ProductionID relationship to FK fields I added in each related table. I believe this is correct with what you posted yesterday.

Table structure. I understand I do not want to duplicate data. The only tables I kind of disagree with is my asphalt tables. Using the logic you posted about if a material is used everyday or not. Each asphalt tank can contain a different grade material and more likely than not only one will be used per production day.

Also from our previous post I’d like to expand to add more information so seems logical to give each ingredient a table rather than re work “combined” data tables later.

[twinnyfo]

Let meknow when you have the Aggregate figured out and working properly.

[NeoPa]

I'd jump in but I can see you're getting the best help possible already Williamson.

Stick with it and trust the guidance you get. Like being on a roller-coaster. It can be scary at times but you can learn to relax if you know you're in good hands that are ready to redirect you if you go astray.

[williamson1979]

Yeah it’s tough. Still wrapping my mind around things. Learning enough to create more mistakes. I guess that’s how you learn though.

[NeoPa]

Actually, you're probably far better off being disciplined about it and taking one clear step at a time, but I understand that's very difficult for many people. It's the easiest and least frustrating route through any learning process but seems nevertheless to be beyond most people (Not just you by any means).

Obviously I recommend that approach, but nobody here will criticise if you do it the normal way and blunder through trying the next step before you've got the earlier ones properly mastered. Learning can be exciting and of course you want to see where your new understanding will get you. Normally that's the equivalent of mildly cracked shins, but we do it because we love it, and most of us can deal with cracked shins from time to time. We can enjoy the journey even if it isn't smooth and comfortable all the time.

[williamson1979]

I’ve definitely got a better understanding but I’ve always learnt new things by trial and error. Which isn’t always the most practical way. I know I’m driving twinny insane. I think the biggest obstacle at the moment is my lack of proper terminology. It’s really hard for me to get what I want to say across in a way that makes sense. What I’m needing it for makes perfect sense to me but to someone reading I’m sure it makes as little sense to them as this sql/vba makes to me.

I know access screws up the sql statements pretty easy once I try to make changes. So I did learn to look at that once something “stops” working as it was or asking parameters. So twinny you did get something into my brain.

But anyway everyone here has been great. Even if I still have aspects of my dB incorrect I’ve still learned things I didn’t know.

[williamson1979]

Twinny. Soon as I add the calculated fields to the query it stops working. By stopping working it displays no data. I copied what you did. I’ve triple checked that I did not mistype the field names.

Agg table ID as interger. Both agg ID and aggType set as PK. Production relationship enforced to agg ID

Subform set as continuous, main form single. I have the labels in the header, data handling boxes in the body and sum boxes in the footer. I edited parameters so it displays as your example with the exception you edited the default color, font.

I do not see any unrelated info in the sql in query view.

Any ideas?

[williamson1979]

I’ve never had issues with queries before but this is the first using 2 tables so there’s a obvious step that I did not notice in your example I’m assuming.

[NeoPa]

I'm going to make a quick suggestion here.

Take your current SQL that isn't working as you've just described. Rip out as much as you can that doesn't affect the problem. We want to see the problem itself, and as little else as we can get away with. Format what you have so that it's legible and then include it in a post that describes as clearly as you can what is wrong with it.

We don't need to work on a big complicated query if we don't have to. This is often a great technique to use whenever you have problems. Strip it down to the bare essentials without losing the essence of what's wrong. Format it so that it's legible. With this you have something you can work on without all your attention being focused on looking at elements that are irrelevant and without having to work so hard at reading what you're looking at.

Sharing it here also allows us to look at it in the same way. With more experience we may be able to help. I often find that once someone has gone through that process though, they understand what the problem is without further guidance.

[twinnyfo]

Your Query should look something like this:

SELECT tblAggregate.ProdID, 
       [PaidTons]+[WasteTons] AS TotalTons, 
       tblAggregate.AggType, 
       tblAggregate.AggU, 
       [AggU]/[TotalTons] AS AggP, tblAggregate.AggR
FROM tblProduction 
INNER JOIN tblAggregate 
ON tblProduction.ProductionID = tblAggregate.ProdID;

[imgnothumb]https://bytes.com/attachment.php? attachmentid=99 59&stc=1&d=1553 795033[/imgnothumb]

[williamson1979]

OK I have the subfrm aggregates Calculating now. I had to link master and child. So that was my problem.