Using linked forms with tables vs. queries

[jcfraun]

I am a very new Access user, and I have been able to get the information I want from my tables in a form with linked forms and subforms. However, I think it might be an inefficient way to getting where I want to be because updating it is very tedious.

I have two tables -
Attribute Table has Product, AttributeName, AttributeValue
Category Table has Product and Category.

What I need is the ability to select a category and the form to pull up all attributes found in that category (any product). When I click on an attribute, I want two subforms - one that shows me which products in this category have that attribute and one that shows me which products in this category do NOT have that attribute.

Like I said, I have achieved this. However, I had to use several MKTBL queries in order to get the one-to-many relationships needed for the linked forms. As far as I can tell, I can't use queries for the linked forms because I can't define one-to-many relationships using queries.

So, for now, I am doing a MKTBL query to find distinct categories, products and attributes; a MKTBL query to find distinct categories and attributes; a MKTBL query to find distinct categories; and a MKTBL query to find the products without the attribute. So, each time I update the data, I have to delete the relationships with these made tables, recreate them and add the relationships back. It does work, but I'm assuming there has to be a better way.

My other question is whether this can be done more simply or even at all in Crystal Reports, which I'm learning simultaneously.

Thanks for any help you can offer!

 

[Remou]

One-to-many is a standard join in any relational database. There is something very wrong here, have you looked at the Cutomer Orders form in the Northwind sample database that ships with every version of Access (also available for download

http://www.microsoft.com/downloads/...-8DBE-422B-8676-C632D66C529C&displaylang=en)?

You will find it illustrates about half of what you want to do.

You should also post your table schemas here and you will get any amount of advice on setting up suitable queries.

 

[MajP]

It seems to me that you are under the impression that forms and reports are only based on tables. This is almost the opposite of reality. Most forms and reports should be based on a Select Query. Also forms and reports can have filters and sort orders.

 

[jcfraun]

I know there's something very wrong. However, when I try to create a relationship between my original table and a query, it won't give me the one-to-many relationship that I need. Therefore, it won't allow me to do the linked forms (I don't think).

When you say to post the table schemas - do you mind giving me an example. I did look at a lot of those examples. I will look at that one in particular again.

And I do realize that forms and reports can and should be based on queries. It's the linked forms that are throwing me off. Like I said, in order to create the linked forms, I read that I needed the one-to-many relationship and I could not define those the way I needed them unless I converted my query to a mktbl query and used the resulting table.

Maybe my problem is simply: What do I need to do to define a one-to-many relationship between queries or a query and a table? I know that it is, in fact, a one-to-many relationship. I just can't convice Access of that.

Thanks so much for your patience and help.

 

[Remou]

This is a table schema:

tblPersons
ID ->Primarykey
Forename
Surname
Etc

I suspect that you need a form-subform set up, which is often reasonably simple. Did you have a chance to look at the Northwind sample?

 

[jcfraun]

Thanks for the reply. I'm looking now.

I'm glad I posted here, because I'm realizing how ridiculous my setup is. I do need a form-subform set up.

TBL-CategoryNames
Category -> PrimaryKey
CategoryName

TBL-Category
Product-> PrimaryKey
AltProd
ProductName
Category
ProdType
Proper Name
Status-Prod
BusinessUnit

TBL-Attributes
ID (which is an AutoNumber) -> PrimaryKey
Company
HeaderProduct
Buyable
ProductType
CancellationDate
AttributeName
AttributeSeq #
AttributeValue


My TBL-CategoryNames - Category has a one-to-many relationship with TBL-Category - Category

However, the relationship between TBL-Category and TBL-Attributes is AltProd to HeaderProduct and AltProd has duplicates in TBL-Category.

Furthermore, I think my primary key in TBL-Attributes should be a combination of HeaderProduct and AttributeName instead of this assigned ID.

I do appreciate any help!

Thanks.

 

[jcfraun]

I have looked at that example and their data is much cleaner and the process seems more straightforward there.

I want my main form to be the Category - and then the first subform needs to be all the AttributeNames listed for products in that category. The second subform needs to be all of the products (AltProd or Header Product) that have that attribute in that category and the third subform needs to be all of the products in that category that do NOT have the selected attribute.

I did get this to work, but I had to force the data into new tables because of all the messy duplicate values. Even in the TBL-Attributes, the HeaderProduct and AttributeName combo can be listed multiple times with multiple AttributeValues.

 

[jcfraun]

One more very specific question. I have been able to redefine some relationships and I think I'm getting somewhere. However, when I have a form showing all the categories and then a subform with all of the attributes, how can I get the subform to only display unique values?

 

[Remou]

SELECT DISTINCT Attribute ...

Will return one of each attribute, however, it is by field, so:

SELECT DISTINCT Attribute, DateAddded ...

Will return one line for each attribute and date added.

For the most part, you should have link child and master fields set up for the subform control so that you only get a list of relevant attributes.

 

[Remou]

I should add that queries created with select distinct are not updatable, so it is generally better to come up with some other solution.

You may wish to read:

Understanding SQL Joins:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

When can I update data from a query?

http://msdn.microsoft.com/en-us/library/aa198446.aspx

Fundamental Microsoft Jet SQL for Access 2000 (includes download AcFundSQL.exe) :

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

Intermediate Microsoft Jet SQL for Access 2000 (includes download AcIntSQL.exe):

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Advanced Microsoft Jet SQL for Access 2000 (includes download AcAdvSQL.exe):

http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

 

[MajP]

You may want to look at
faq702-5860
for synchronizing forms.
There is some discussion in there about synchronizing two main forms.

 

[jcfraun]

Thank you all for the tips! I did succeed in redesigning my form and now I have a couple of new questions. I will ask them here first before posting a new thread.

Now that my form is running off of the original large tables, it takes a while to update the values when I select anything in the forms. I'm assuming this is normal, but are there tips for keeping the query calculation time to a minimum?

Also, I have several linked subforms and within those subforms, I have nested subforms which I have set to display as datasheets. When a user clicks on the plus sign to look at the subdatasheet, is there a setting I can use to make this information float on top of everything else? Otherwise, it isn't viewable.

And one final question - how can I display a count of only the records that show up on one of my linked subforms? This subform is linked to another subform using two fields. Therefore, when I add a count of the records in the subform, it is counting all of the records in the underlying query, not just the displayed records. Any tips?

Thanks!