Need Query - Form Relationship Help

[Access_Headaches]

I am using a query that is computing net daily calorie intake. I also have a sub
query computing the average daily intake based off of that field. My problem
is I cant make the Average Field work on my form since the form is sourced
from the main query. I tried making a relationship between the two queries
(net calorie intake field / avgof calorie intake field) but cant make it work.

Also, if I use the SIGMA button and use Average on my main query, my form
locks up because of the aggregation in the main query.

Any help would be great, also I'm new to this forum so if I can post a
picture, let me know and I'll do so asap.

If anyone need more pics, let me know.

 

[boblarson]

1. You don't define relationships using queries; only tables.

2. If you just want to include the avg from that query on a form, you can either use a subform or a dlookup to get the value. You would not include it in the form's recordsource so as to keep it updateable.

 

[Access_Headaches]

Not a fan of sub forms but how do you do a dlookup? (only been learning access for two weeks with the help of lynda.com)

 

[vbaInet]

You need to start becoming a fan

DLookup() function:

http://www.mvps.org/access/general/gen0018.htm
http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[Access_Headaches]

Ahhh...Got it, thanks again.

(I'll be honest, my sub-form dislike comes from ignorance. I can't make my sub-forms look nice. There always large and take up my precious pop-up form space. Even after I clean up the excess space in design view and drag them over to my main form, they add the form header and form footer with my detail in the middle.)

I can do wonders in Photoshop and Premiere pro but I spend most my time wondering in Access.

 

[boblarson]

Not a fan of sub forms

Why not? They are an integral part of Access and a properly designed database structure.

It is kind of like saying that "I'm not a big fan of automobiles - I will only use a bicycle."

Yes, a bicycle may get you where you need to go but to do any heavy lifting may require an automobile (or larger) in order to get something home unless you want to spend hours/days dragging it home with your bicycle.

 

[Access_Headaches]

Your answer lies above...

 

[boblarson]

Ahhh...Got it, thanks again.

(I'll be honest, my sub-form dislike comes from ignorance. I can't make my sub-forms look nice. There always large and take up my precious pop-up form space. Even after I clean up the excess space in design view and drag them over to my main form, they add the form header and form footer with my detail in the middle.)

You just need some training on what you can do with them. In fact, if formatting is set right you can make it so that you might not even be able to tell that there is a subform on the form.

Check this form out and tell me where the subform is:

 

[Access_Headaches]

Quite impressive Sir, Quite impressive indeed. I'm learning access for work, we have a very basic database and I'm supposed to learn more in-depth material. Right now, I'm just running reports and doing minor modifications. Lynda.com has only extremely basic material available, do you know where I can get more in-depth tutorials?

 

[Access_Headaches]

P.S. My main complaint with lyda.com is they have no Visual Basic Help or VBA Code Builder tutorials at all. This is where limits disappear in what you can do. (As you Know)

 

[dkinley]

Here is a sample I just worked up for another user. As in Bob's post, I was helping the poster to hide subform from the user - but the implementation of the subform makes life much easier.

Although I should note to not this type of structure because of the type of implementation is specific for the user and I am working with the user to potentially modify the structure.

Just offering a sample to demonstrate .....

-dK

 

[boblarson]

Quite impressive Sir, Quite impressive indeed. I'm learning access for work, we have a very basic database and I'm supposed to learn more in-depth material. Right now, I'm just running reports and doing minor modifications. Lynda.com has only extremely basic material available, do you know where I can get more in-depth tutorials?

Okay, so here's the answer to which one was the subform:

As for coding for subforms, it is not as hard as it may seem but you need to get some concepts down. First up, see if this page here from my website helps you at all. Then go to this one for a reference on referring to things.

 

[Access_Headaches]

That was exponential helpful. Getting the right properties to show to enable me to turn off the header viability off was quite nice as well! Sub-Forms are quite functional once you get a small understanding of them too.

Back to my original question, any other sites you know of with "Good" tuts for access? I know so little when it comes to the VBA coding (before update/after update ext) its not even funny. I set one thing up that forces caps in all fields before updating but never got a grasp on what I was truly doing.

 

[boblarson]

Try this one out:

Access 2007 VBA

Access 2007