Create a custom form that references particular cell from query

[ian87]

Hello again fellow access users and overlords.

My problem today is that I want to create a form that allows me to view all fields from a query but then group certain records into parent boxes on a form. Basically my idea is to create text boxes that reference a certain field which will allow me more freedom to move things around on the form without damaging any relationships. The form will be used to check overarching categories add up to the values of the cells that i have in my query.

E.g.

Total revenue from sandwiches (new cell that will be a sum of fields in the query)
New field in form:

Total Sandwich revenue SUM(bacon and cheese, chicken, cheese)

fields in query

Bills Sandwiches bacon and cheese 25.50
Bills Sandwiches Chicken 34.60
Bills Sandwiches Cheese 29.80


The above was purely a simplified version of what I want. FOR WHAT I REALLY WANT PLEASE SEE ATTACHED IMAGE. I can not redesign the tables as I am now at a stage where it is too late.


Thanks for your help.

 

[John Big Booty]

Have a look at the various Domain Aggregate Functions. DSum() is probably the one you want.

 

[ian87]

Hey Jon,

I knew the anecdotal story about sandwiches wouldn't be the best one to use but I was trying to illustrate my problem. I'll give you more of an insight and maybe you can help. Please look at the picture attached.

 

[vbaInet]

We all know Accenture

You can use the DLookup() function:

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

 

[John Big Booty]

In your image the Service Line CMS appears only once for each vendor, is this correct?

If so perhaps DLookup() would be a more appropriate function for you to use

You might also want to read up on Data Normalisation.

 

[vbaInet]

Sorry John, thought you were away

 

[John Big Booty]

Sorry John, thought you were away

No problem, we're both on the same wavelength

 

[John Big Booty]

... going to bed now, so it's all yours for the next seven hour or so

 

[vbaInet]

... going to bed now, so it's all yours for the next seven hour or so

Enjoy!!!

 

[ian87]

Hey guys,

I tried using Dlookup but it just says #Name?

=DLookUp([qryCMS]![Est2009],[qryCMS],[qryCMS]![ServiceLine]='CMS')

The above was posted in a form so i could get the result and edit it.

I have a qry with the following fields as in the previous picture

Vendor ServiceLine Est2009
Accenture CMS 440


I want the dlookup to show the cell that has 440 in it.

Any tips on why it's not working in the form?

 

[ian87]

I agree with you about data normalisation and realise that is what makes this difficult Jon, the problem is that I now have over 12947 records and have already designed 12 forms that feed from it. I would rather just try and get this fixed in the same format because people are trying to add new forms all of the time, I have already redesigned the db a few times. I'm not complaining to you, I'm just saying if it's possible I would rather not redesign once again

 

[vbaInet]

I tried using Dlookup but it just says #Name?

=DLookUp([qryCMS]![Est2009],[qryCMS],[qryCMS]![ServiceLine]='CMS')

Perhaps you want to look at the syntax again in the first link provided. You will see what you're missing.

The above was posted in a form so i could get the result and edit it.

FYI: You will not be able to edit this value directly.

 

[vbaInet]

I agree with you about data normalisation and realise that is what makes this difficult Jon, the problem is that I now have over 12947 records and have already designed 12 forms that feed from it. I would rather just try and get this fixed in the same format because people are trying to add new forms all of the time, I have already redesigned the db a few times. I'm not complaining to you, I'm just saying if it's possible I would rather not redesign once again

You will be better off fixing it now than leaving it till later because you will end up running into much bigger problems. Creating fields for every year is not the way to go. Normalise then have a look at a Crosstab Query.

 

[ian87]

Hey guys, I have a solution. You guys are going to hate it , it's so messy. I'll just take a screenshot and send it over, one sec.

 

[ian87]

ENJOY!

I have to type something to make the message long enough to post...

 

[vbaInet]

Something like this would be more appropriate:

=Sum(IIF([ServiceLine] = 'CMS', [Est2009], Null)) + Sum(IIF([ServiceLine] = 'Govt.', [Est2009], Null))