Viewing a calculated query field on a form

[klarae99]

Hello, I am working on an Access 2003 Database for inventory control. I am setting up a form (frmProducts) for the viewing and adding of product information. I have several tables that this information is coming from:

tblProd with fields: item(PK), CatID(FK), List Price and other not fields not related to this question

tblCat with fields: CatID(PK), Category, Tax Rate

I have a query:qryListPrice with fields tblProd.ListPrice and tblCat.tax and a calculation field List Price to calculate the price including tax.

I would like to be able to view the list price on my frmProducts. I have created an unbound textbox on the form and made its control source =[qryListPrice]![List Price] but when I view the form the field shows #Name?. I know that I can do the calculation right in the field, but I will use the list price in many of my reports and thought it would be better to have all references to list price calculated from the same place.

Can anyone help me get the calculated value of the field to reflect the query calculation, or is it a better idea to simply do the calculation in the text box? Any and all help and advice is greatly appreciated.

[FishVal]

Hi, klarae.

To make it you need to set Form.RecordSource to [qryListPrice], then calculated[List Price] field will appear in ControlSource dropdown. Certainly you need to retrieve to the query all fields bound to the form controls.

Regards,
Fish

P.S. Not sure whether it is a better idea to calculte value in TextBox control. Sometimes yes, sometimes no. In that kind of situation I would prefer to do it via query as SQL performs faster than control calculation.

[klarae99]

Fish,

Thanks for your quick reply, I really appreciate any help you can give me. I have tried your suggestion of changing the Form.Recordsource to [qryListPrice] then selecting list price in the control source. When I do this and switch back to form view, the entire form is blank, no fields, no lables, nothing.

Currently all of the other fields are linked to tblProducts. I had tried to make a queary with the information from tblProducts, tblCatigories, tblSubcategories, tbl Events and tbl Vendors that included all the fields that I wanted on my form but ran into the error that the queary was too complex.

I then tried to 'nest' a couple of queries to get the same fields and could make that work (sort of) but I had issues creating my comboboxes and I could not get a new record to appear in the form, even when I changed the form to data entery. Which is why I went back to creating the form from the tblProducts, and using the other tables as combobox control sources.

I know that I have read in various threads that it is better to create forms from queries, which is part of the reason I tried in the first place, but I've been working on this database for two weeks now and my boss seems to think it is taking me to long.

Any advice would be greatly appreciated. Thanks so much for your help!

[FishVal]

Hi, klarae99.

I doubt whether you can join all the tables to one query and put it to one form.
Start with some less ambitious task. You had form which was populating [tblProducts], you wanted to add one calculated field. So create a query from only necessary tables joined retrieving all the fields from [tblProducts] and one calculated field you need. Set the form RecordSource to the query and set form controls ControlSource to correspondent fields of the query.

[klarae99]

Thanks so much for your help, I had a complete mind block as to how to create the queary to include all the fields I needed (because the information comes from so many different tables); until I read your reply. I didn't think to create the query on just the information in the products table (with all the ID Foreign Keys to the other tables) and then change the rowsource to view the names instead of the ID#, just like I did on the form based on the table. That makes my life so much easier. Thank You!

I do still have an issue however, when I create the form based on the simplified queary I cannot add a new data, I can only edit the items already in the database. Is there a way I can set it up to do both? I really appreciate your continueing help.

[FishVal]

Well.

You should definitely think how to represent data from many tables. Joining all of them to one query is not a suitable way. It will complicate data managing, open door for errors and you can end upon table joining with non-updatable query just as you have so far.
I strongly suggest you to divide your form into main form and one or many subforms. Post your tables metadata and I will be glad to make you suggestions of how form/subform interface may be organized.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Fish

[klarae99]

Fish,

I was unsure how to get the MetaData from Access directly so I typed it up based on the fields in my tables, I am not sure I got all the Types 100% correct but here are (all of, I wasn't sure what you would need) my tables in the format you requested.

Table Name = tblProd
Field; Type; IndexInfo
Item; Number; PK
VendorID; Number; FK
Description; Text
Price; Text
CatID; Number; FK
SubCatID; Number; FK
Discontinued; Y/N
Reorder; Number

Table Name = tblVen
Field; Type; IndexInfo
VendorID; AutoNumber; PK
VendorName; Text
VendorCode; Text
Address; Text
Address2; Text
City; Text
State/Country; Text
PostalCode; Text
ContactName; Text
E-mail; Text
Website; Text
Phone; Text

Table Name = tblCat
Field; Type; IndexInfo
CatID; AutoNumber PK
Category; Text
Tax Rate; Number

Table Name = tblSub
Field; Type; IndexInfo
SubID; AutoNumber; PK
CatID; Number; FK
SubCategories; Text

Table Name = tblTrans
Field; Type; IndexInfo
SubProdID; AutoNumber; PK
Date; Date
EventID; Number; FK
Received; Number
Sold; Number
Our Cost; Currency
Discount; Currency
Item; Number FK

Table Name = tblEveCons
Field; Type; IndexInfo
ContactsID; AutoNumber; PK
CompanyName; Text
FirstName; Text
LastName; Text
Address; Text
City; Text
State; Text
Zip; Text
Title; Text
WorkPhone; Text
FaxNumber; Text
Email; Text

Table Name = tblEveInfo
Field; Type; IndexInfo
Event ID; AutoNumber; PK
Name; Text
EventContactID; Number; FK
StartDate; Date
EndDate; Date
Location; Text
StartTime; Time
EndTime; Time

The form that I am working on for data entry and editing currently consists of a form with a subform, they are listed below:

Form Name = frmProd
Table; Field
tblProd; Item
tblProd; VendorID (Rowsource tblven; 2 col, 0;1 width)
tblProd; Description
tblProd; Price
tblProd; CatID (Rowsource tblCat; 3 col. 0;1;0 width)
tblProd; SubCatID (Rowsource qrySub, Requeary on Enter)
tblProd; Discontinued
tblProd; Reorder
unbound; List Price

SubFormName = frmSubProd
Table; Field
tblTrans; SubProdID (not visable)
tblTrans; Date
tblTrans; EventID (Rowsource tblEveInfo 2 col, 0;1 width)
tblTrans; Received
tblTrans; Sold
tblTrans; Our Cost
tblTrans; Discount
tblTrans; Item (not visable)

With your previous post inspireing me to simplify my query I created one that consists of the fields below to base my form on.

Query Name = qryQuick
Table; Field
tblProd; *
qryListPrice; List Price

This queary returns all the products entered into the table along with the list price that I calculated with the List Price Query that we have already discussed briefly. The form that I tried to create using this queary would be identical to the frmProd that I already have but would be based of qryQuick not tblProd. The fields that I have placed so far work fine but there is not the option to add new data. I hope that this is not an overload of information and that it will help you 'see' my database (which I know is not 100% Normalized but right now it will have to do so that I can get it functioning by next week, also I should not ever have to combine the Event and Vendor individuals)

Thank you again for taking the time to look at this, I really appreciate all the help!

[FishVal]

Excellent.

Very nice and comprehensive post. I'll take a close look on it in a hour, when will get home.

[klarae99]

Fish,

I'm glad it is what you wanted. I'll look for your reply tomorrow. Thanks!

[FishVal]

Hi, klarae.

My suggestions will be the following.


Section 1: Tables.

Generally your table structure is good, but not perfect.
Here is the list of changes you should make to the table structure.

• 1. Very impotant (breaking basic db design rules)
  • [tblProd] stores duplicate reference to Category: [CatID] and [SubCatID]. These are already related one to other with [tblSub], so [tblProd].[CatID] is not
    needed as long as it stores duplicate relation of [CatID] and [SubCatID]. Storing duplicate information requires synchronizing mechanism. For example: you need to relate subcategory to another category, for this purpose you change correspondent record in [tblSub], but all [CatID]/[SubCatID] relations in [tblProd] will remain intact, to correct this you'll need to run update query on [tblProd]. The rule "not to store duplicate information" is one of the basic db design rules
• 2. Less important / recomendation (not very critical, but will improve overall db design and will help in maintaining data integrity)
  • [tblVen] contain fields [Address] and [Address2]. Why not to move them to separate table (let us say [tblVenAddr])? [tblVen] 1->many [tblVenAddr]
  • [tblVen] and [tblEveCons] contain fields [City] and [State]. I see here 1->many hierarchy [State]->[City]->([tblEveCons], [tblVen])
  • [tblEveInfo] stores Data and Time in separate fields. This actually an overkill as Date/Time field stores both. Though I would like to store Date/Time in same field it is not critical at all, just an issue of programming style.

Section 2: Interface.

NB: statements like "form based on [tblProd]" mean form which controls are bound to [tblProd] fields only, form recordsource may be [tblProd] or any table join query returning [tblProd] fields with additional calculated fields

At the bottom of your tables structure hierarchy you have [tblTrans] which is in many-1 relationship with [tblProd] and [tblEveInfo] implementing many-many relationship between them. Here we have the most tricky part of interface: subform based on [tblTrans] should have two main forms - one based on [tblProd] and one on [tblEveInfo]. Though Access forms themselfs don't support such kind of linking this may be easily done for subforms set with some code.

So, I propose the following:

Option 1.

The main form is just a background where the following subforms are placed:

• form based on [tblTrans] (let us say [frmTrans])
• form based on [tblProd] (let us say [frmProd]). [tblProd] fields [Vendor] and [SubCat] are bound to comboboxes. [frmProd] has an additional unbound combobox with RowSource=tblCat, it is used to filter RowSource of [SubCat] combobox on category modification and is being populated with relevant category based on existing [tblProd].[SubCat] value.
  NB: comboboxes cascade will not work in datasheet or continious form view unless table structure will not be adjusted correspondently.
  There are several special methods to input via combobox data not existing in its RowSource table simaltaneously appending it to RowSource table, so don't worry so far about entering new Vendor or/and Category/Subcategory.
• you have two options to implement interface on [tblEveCons]-[tblEveInfo]-[tblTrans] relation (let us say this will be [frmEvent])
  • form based on [tblEveCons]-[tblEveInfo] join query
  • form based on [tblEveCons] with subform based on [tblEveInfo]

These 3 forms are placed on the main background form (you may use tab control to save space). A simple code will populate [frmTrans] with records related to current [frmProd] and [frmEvent] record.

Option 2.

Unlike the above symmetrical interface you may place [frmTrans] as subform on [frmProd]. [frmTrans] contains combobox bound to [tblTrans].[Item]. [frmEvent] will be opened when you'll need to enter to combobox new info not existing in [tblEveInfo] and/or [tblEveCons].

Option 3.

Just the same as option 2 where [frmTrans] and [frmEvent] have changed places.

Let me know whether it all makes you sense and whether you have additional questions/wishes/suggestions on the design before will start design itself.

Regards,
Fish

[klarae99]

Fish,

Sorry about the delay in replying, I was out of the office on Friday and didn't get a chance to really review your post from home so here I am now.

If I am understanding you correctly regarding Sec 1 comment 1, I do not need the field tblprod[catID] because the SubCatID is dependent on the CatID so even if I do not have the CatID in my tblprod, the category information will still be linked to each item by its SubCatID? Will this relationship still work if I do not use the SubCategory field in a report for example. Or if I try to do a search for items within a certain category without any concern about subcategory?

Regarding Comment 2:

In tblVen Address and Address2 are for the same location, not two different addresses. For Example Joseph O'Connor lives at (Address) 5763 Celtic Dr. (Address2) Building 7 Room 9, so I don't think the tblVenAddress is really necessary.

I had not thought about a seperate table for state and city and then linking that to the two contacts tables. The only difficulty that I see potentially arising is that Ireland does not use the field state in its delivery addresses, so Irish Addresses would not be represented in the first table. Many, but not all, of our vendors are in Ireland so I'm not sure how many complications that would bring up. Right now it is working fine for me as is and I will probably leave it unless there is a big reason to change it.

I do know that there are fields that store the date and time together and honestly did not think about using them when I set up the table but after thinking about it a while I think I like the current set-up for two reasons; the first is multi-day events, ex. the festival runs from 10-5 on wends, thurs and fri. I do not want to give the impression that it will open at 10 on Wends and closed at 5 of Fri, implying that at 7 Thurs it would be open. Also I think it would be harder for the user to enter the data, since they would have to know the format for the data entry (ex. date in mm/dd/yyyy then comma then time in 12 hour increment with AM/PM or is it mm/dd/yy no divider then time in 24 hour increment or is it time first etc.) . So I think I will leave this alone, again unless there is a real reason to change it.

Section 2

What does NB stand for?

I looked at my form that I currently have set up for data entry (the one I based on tblProd). I changed the recordSource to qryQuick and was pleased to see that all of the fields still functioned. Which was great but again I lost the ability to add new records.

In regards to option 1.

frmTrans I understand this Fine

frmProd- I can see a combobox for Vendor and SubCat (both are ID FK fields that I would set up with a record source to the PK table?) I have an issue with the unbound combobox if I am understanding you correctly. It would be bound to tblCat, I get that, my issue is that it is being populated by the existing tblProdSubCat Value. So as I am understanding it, you would select the subcategory and the category would autofill. My issue is that some categories (ex. Crystal and China) have some of the same subcategories (ex. vase, bowl) I can see this potentially causing complications.

I am confused again by your NB comment but trust it will be explained in time.

I think I like Option 2 in regards to the form for Event Information assuming that you mean that the Event Names will be listed in a combobox on frmTrans and that if I need to Add a new event or event contact I would open a different form completely.

I do not think I would like a form where the product Information had a subform about the various events where it sold and then having to go to a different form to enter sales and reciept of the items, It seems very disjointed.

So if my understanding is correct so far I think that Option 2 is what I would like to do. (there would also be forms for changing/adding vendor and category info ect. correct?)

Thank you for your continued assistance with this database, I really appreciate that you are looking beyond my original question and helping me with issues I wasn't even aware I had.

[FishVal]

My issue is that some categories (ex. Crystal and China) have some of the same subcategories (ex. vase, bowl) I can see this potentially causing complications.

Hi, klarae.

Sorry for the delay.

I suppose it will be more effective to split development process to several more or less independent parts and perform then one-by-one.

So, in this post I'm going to discuss the most important issue in db design - tables structure.

My issue is that some categories (ex. Crystal and China) have some of the same subcategories (ex. vase, bowl) I can see this potentially causing complications.

This is a classic many-to-many relationship - one category may be related to many subcategories and one subcategory may be related to many categories
Your current table structure doesn't reflect this type of relationship.
It is normally done with the third table where each record contain FKs of the both tables needed to be related many-to-many.

Like this.

Table Name = tblCat
Field; Type; IndexInfo
CatID; AutoNumber PK
Category; Text
Tax Rate; Number

Table Name =tblCatSub
Field; Type; IndexInfo
keyCatSubID; Autonumber PK
CatID; Number; FK(tblCat)
SubID; Number; FK(tblSub)

Table Name = tblSub
Field; Type; IndexInfo
SubID; AutoNumber; PK
SubCategories; Text

Table Name = tblProd
Field; Type; IndexInfo
Item; Number; PK
VendorID; Number; FK
Description; Text
Price; Text
keyCatSubID; Number; FK(tblCatSub)
Discontinued; Y/N
Reorder; Number

Note, [tblProd] contains FK field from [tblSubCat]. This ensure that a product belongs to valid category/subcategory combination. Plz don't be confused with how it will go to the form/report. Tables and forms/reports are quite different parts of db design. Here for example you may obtain recordset for report with category/subcategory/productinfo via table join.

Form is somewhat different issue. I suggest you to use cascading comboboxes for category and subcategory and this will require different logic.

Though the scheme above is nice, it will be difficult to use it if you want to design products form in datasheet or continuous forms view. This will cause troubles with cascading comboboxes.

The following tables structure will be a better solution. Though it may performs slightly slower in some cases (I suppose this will be negligible), it definitely fits all needs and will make development easier.

Table Name = tblCat
Field; Type; IndexInfo
CatID; Text; PK
Tax Rate; Number

Table Name =tblCatSub
Field; Type; IndexInfo
These two fields are combined in multifield PK
CatID; Text; FK(tblCat)
SubID; Text; FK(tblSub)

Table Name = tblSub
Field; Type; IndexInfo
SubID; Text; PK

Table Name = tblProd
Field; Type; IndexInfo
Item; Number; PK
VendorID; Number; FK
Description; Text
Price; Text
--------------- Multifield key relation to tblCatSub
CatID; Text; FK(tblCatSub)
SubID; Text; FK(tblCatSub)
---------------
Discontinued; Y/N
Reorder; Number

I am confused by your NB comment but trust it will be explained in time.

N.B. = Nota bene

[klarae99]

FishVal,

Thank you for your imput on my table structure. I have a couple of questions for you about how necessary it is that I change them now. As I had mentioned earlier my boss would like for me to start data entery by tomorrow. If I start changing my table structures at this point I will have to rework many of my forms and reports to draw their information from my revised table structure. With a deadline of tomorrow I don't think that it is very viable to do that at this point. However, if the current configuration WILL NOT work, and will result in inaccurate information being stored than it will have to be changed. I guess my question is how crucial is it that I make these structural changes? Can I make it work the way it is? As it stands each Category/Subcategory has a unique SubCatID number. Your continuing advice is appreciated.

[FishVal]

FishVal,

Thank you for your imput on my table structure. I have a couple of questions for you about how necessary it is that I change them now. As I had mentioned earlier my boss would like for me to start data entery by tomorrow. If I start changing my table structures at this point I will have to rework many of my forms and reports to draw their information from my revised table structure. With a deadline of tomorrow I don't think that it is very viable to do that at this point. However, if the current configuration WILL NOT work, and will result in inaccurate information being stored than it will have to be changed. I guess my question is how crucial is it that I make these structural changes? Can I make it work the way it is? As it stands each Category/Subcategory has a unique SubCatID number. Your continuing advice is appreciated.

Hi, klarae.

I don't want to force you in your decision but your current table structure (the variant I know) is not suited for what you've stated.

My issue is that some categories (ex. Crystal and China) have some of the same subcategories (ex. vase, bowl)

This means you will not be able to check during input whether subcategory corresponds to parent category.

I would go with the 2nd variant I've suggested in the last post.
It will require minor changes in Products form and it will be easy to handle data in datasheet view (this is not trivial with cascading comboboxes)
As you see CatID and SubID are stored in tblProd as text fields, so queries for Form/Report Recordsourses and combobox Rowsourses will be simpler.

The advantage is that you have table relating subcategory to category many-to-many. Simultaneously this table join them into unique multifield PK which appears as multifield FK in tblProd. Referential Integrity on this relation should be set to "Cascade update". This will ensure all records in tblProd to have only those CatID/SubID combinations present in tblCatSub.

I think that the only thing you will have to do is to change design of CatID and SubID comboboxes in product form.

Table Name = tblCat
Field; Type; IndexInfo
CatID; Text; PK
Tax Rate; Number

Table Name =tblCatSub
Field; Type; IndexInfo
These two fields are combined in multifield PK
CatID; Text; FK(tblCat)
SubID; Text; FK(tblSub)

Table Name = tblSub
Field; Type; IndexInfo
SubID; Text; PK

Table Name = tblProd
Field; Type; IndexInfo
Item; Number; PK
VendorID; Number; FK
Description; Text
Price; Text
--------------- Multifield key relation to tblCatSub
CatID; Text; FK(tblCatSub)
SubID; Text; FK(tblCatSub)
---------------
Discontinued; Y/N
Reorder; Number

[klarae99]

FishVal,

Thanks for your imput.

Before begining the modifications to my tables I went back and reviewed the entire post to make sure that I had communicated everything the way I wanted to and to see if I fully understood everything you had suggested to me. In your post that responsed to my table structures with PK, FK labeled etc, you made a suggestion about a form based on [tblProd] that was the start of our discussion of many to many relationships. I just would like to go through this suggestion line by line to insure that we are really understanding each other.

this discussion is all based on the assumption that I eliminate the CatID field from my tblProd.

Your second sentance:
[tblProd] fields [Vendor] and [SubCat] are bound to comboboxes.
My thoughts: the Fields in tblProd are [VendorID] and
[SubCatID] which are both number FK
fields, so the options for the combobox list
would have to come from tblVendor and
tblSubCat with a column count of 2 and the
bound column being the ID# with the
column widths set to 0;1 so that the text of
the name was available to select instead of
the ID#

Your third sentance
[frmProd] has an additional unbound combobox with RowSource=tblCat, it is used to filter RowSource of [SubCat] combobox on category modification and is being populated with relevant category based on existing [tblProd].[SubCat] value.
My thoughts:
A.this unbound combobox linked to tblCat would be
set up almost identically to the one above so that
we could view the text and not the ID#.
B. The selection in this box would limit the list available
in the combobox for subcategory discribed above.
C. This is where my confusion originally set in. When
I first reviewed this I was concerened that when
entering data I would only have the option of
selecting a subcategory and that the category
would be deduced from this selection. Upon
rereading this I believe that the intention of this was
to link the Category back to exsisting enteries,
these exsisting enteries would have been entered
by selecting the Category and then the
SubCategory from the comboboxes; but only the
SubCategoryID could be stored in the tblProd.
These pre-exsisting enteries would reference the
number stored in SubCatID and use it to fill in the
subcategory combo box and the category
combobox would 'search' the subcategory
combobox for its related link. If this is a more
accurate understanding than we shouldn't really
need to change the structure because each
subcategory/category combo has its own unique
subcategoryID (ex. Crystal, Vase is SubCatID15
where ProcilineVase is SubCatID23.) My concern
initally was that if you were to list my subcategories
Vase would be listed twice and the person entering
the data would have no way of knowing which one
they needed.

Let me know if I still need to redo my table sturcture and if I do I will start the reconstruction. Thanks for your continuing efforts on my behalf.