Bound Columns, Relationship & Join Types, Parameter requests

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Susan C. - 26 May 2005 18:18 GMT

Please bear with me for the basic questions. See explanation below for
reasons I'm posting anyway.*

I'm tracking our products for kosher status & proprietary status, using
Access 2003.
Tbl Briefs: "Brief#"; "Item#"
Tbl CustIDs: "Possible Customer Name"; "CustID"
Tbl Proprietary: "Item#"; "Proprietary Customer"
TblMain ITEMS: Primary Key "Item#"; "Item Name"; "KStatus"; "CustID"
Item# joined 1:M from TblMain ITEMS:Tbl Briefs but Join Properties has All
records from ITEMS, some records from Briefs
Frm DATA ENTRY w/SubFrm Briefs

Goal: Input Item#, Item Name, Kosher Status, Proprietary customer in DATA
ENTRY
User chooses from list of possible customer names, which returns the proper
custID. Briefs including that Item# listed.

Problems: (after many many permutations)
1) not permitted to delete CustID from tblITEMS, due to a relationship which
I deleted long ago;
2) Form keeps asking for parameters every time I try to look at anything;
3) When I did try to use tblCustIDs as row source for tblITEMS.CustID, no
matter what I put for bound columns & number columns & column widths, it
always retained Possible Customer Name, not the actual CustID.
4) usually can only establish "indeterminate" relationships, whether I show
tables & drag fields or Create New relationship

*Working as a temp in a company w/no trained Access users; limited in how
many hours I can study up on this. Have tried Access manual, Northwind &
Garden Company tutorials on my own time. Can't use more company time
continuing to search discussion posts & other links posted by MVP's.

Reply to this Message

Susan C. - 22 Jun 2005 15:08 GMT

hi, just wondering why no one has touched this one. Everyone else seems to
get a reply within hours!

sad Susan

Reply to this Message

Van T. Dinh - 22 Jun 2005 15:25 GMT

Well ... there is nothing to touch. I can't see another post in your name
or of the same subject in this newsgroup.

Signature

HTH
Van T. Dinh
MVP (Access)

> hi, just wondering why no one has touched this one. Everyone else seems to
> get a reply within hours!
>
> sad Susan

Reply to this Message

John Vinson - 22 Jun 2005 17:54 GMT

>Please bear with me for the basic questions. See explanation below for
>reasons I'm posting anyway.*
[quoted text clipped - 8 lines]
>records from ITEMS, some records from Briefs
>Frm DATA ENTRY w/SubFrm Briefs

A couple of concerns: blanks and special characters such as # should
be avoided in fieldnames. You can get away with it, usually, by ALWAYS
enclosing the fieldname in [square brackets] but it's really
preferable to use alphanumeric and underscore only.

>Goal: Input Item#, Item Name, Kosher Status, Proprietary customer in DATA
>ENTRY
>User chooses from list of possible customer names, which returns the proper
>custID. Briefs including that Item# listed.

You'ld use a Form with a combo box to store the ID - is this a
problem?

>Problems: (after many many permutations)
>1) not permitted to delete CustID from tblITEMS, due to a relationship which
>I deleted long ago;

Maybe you didn't actually delete it! To delete a relationship, open
the Relationships window; show all relationships; and select the *JOIN
LINE* (not the table icon). The "orphan" table icon can then safely be
deleted.

>2) Form keeps asking for parameters every time I try to look at anything;

What's the Form's Recordsource? Please post the SQL. Is there anything
in its Filter or OrderBy properties? What specifically is it asking
for?

>3) When I did try to use tblCustIDs as row source for tblITEMS.CustID, no
>matter what I put for bound columns & number columns & column widths, it
>always retained Possible Customer Name, not the actual CustID.

"retained"? Are you trying to put a combo box IN A TABLE? If so,
don't; combo boxes are vital on forms, but are a pernicious misfeature
in Tables.

>4) usually can only establish "indeterminate" relationships, whether I show
>tables & drag fields or Create New relationship

You must have a unique Index, such as a Primary Key, on the joining
field in the "one" side table in order to get a one to many (or a very
rarely necessary one-to-one) relationship. Do your tables all have
Primary Keys? If so, use them for relationships; if not, define a
Primary Key field.

>*Working as a temp in a company w/no trained Access users; limited in how
>many hours I can study up on this. Have tried Access manual, Northwind &
>Garden Company tutorials on my own time. Can't use more company time
>continuing to search discussion posts & other links posted by MVP's.

"Sometimes you need to take time in order to save time" as my mom used
to tell me.

John W. Vinson[MVP]

Reply to this Message

Susan C. - 29 Jun 2005 18:26 GMT

John, thanks for responding! Of course it took me a week to get back to you,
sorry...

> A couple of concerns: blanks and special characters such as # should
> be avoided in fieldnames. You can get away with it, usually, by ALWAYS
> enclosing the fieldname in [square brackets] but it's really
> preferable to use alphanumeric and underscore only.

thanks for the tip; haven't encountered warnings about them though. don't
want to change it now i'm confused enough already.

> >I'm tracking our products for kosher status & proprietary status, using >Access 2003.
> >Tbl Briefs: "Brief#"; "Item#"
[quoted text clipped - 4 lines]
> >records from ITEMS, some records from Briefs
> >Frm DATA ENTRY w/SubFrm Briefs

> >Goal: Input Item#, Item Name, Kosher Status, Proprietary customer in DATA
> >ENTRY; > >User chooses from list of possible customer names, which returns the proper > >custID. Briefs including that Item# listed.
>
> You'ld use a Form with a combo box to store the ID - is this a> problem?

How weird. I just tried it again, using a query that listed all the info
together. So far it looks good!! Not sure what was different this time; I
thought I had all the same set-up before.

Re the query: Is that the way I'm supposed to pull the info together? After
reading so many posts "condemning" the practice of putting several types of
info into an Excel-type table, I tried to divide mine into separate
two-column tables, using Item# as the primary index where possible (not
possible for TblBriefs). Had a hard time establishing the two "one-to-many"
relationships for that "many-to-many" relationship.

> >4) usually can only establish "indeterminate" relationships, whether I show
> >tables & drag fields or Create New relationship

> You must have a unique Index, such as a Primary Key, on the joining
> field in the "one" side table in order to get a one to many (or a very
> rarely necessary one-to-one) relationship. Do your tables all have
> Primary Keys? If so, use them for relationships; if not, define a
> Primary Key field.

In one table, there is no unique key, so I just tried to add an autonumbered
primary index field but I guess I can't do that after the records are already
entered?

> >Problems: (after many many permutations)
> >1) not permitted to delete CustID from tblITEMS, due to a relationship which
[quoted text clipped - 4 lines]
> LINE* (not the table icon). The "orphan" table icon can then safely be
> deleted.

I had done just that, and there were no more lines visible there unless I
made new ones. But when I tried to delete CustID from a table, that's when
it would give me the relationships error.

> >2) Form keeps asking for parameters every time I try to look at anything;
>
> What's the Form's Recordsource? Please post the SQL. Is there anything
> in its Filter or OrderBy properties? What specifically is it asking
> for?

I can't get into them now. This is the only SQL I can see (I don't know how
to use them anyway)
SELECT [QUERY Brief].[Brief#], [QUERY Brief].[Item#], [QUERY Items 2].[Item
Name], [QUERY Items 2].KStatus, [QUERY Items 2].CustID
FROM [QUERY Items 2] RIGHT JOIN [QUERY Brief] ON [QUERY Items
2].[Item#]=[QUERY Brief].[Item#];

oops I just deleted one of the queries referenced in the above statement.
HOW do I go about trying to solve these problems, in terms of trial & error
experimentation? Do I need to copy the whole database into a different
location & practice there? B/c if I put in trial tables/queries/forms in the
real one I get really messed up. And once I've changed something, I usually
can't get it back again or even reconstruct it.

At this point, I have a whole new set of relationships than I did before,
not sure just how different.

> >3) When I did try to use tblCustIDs as row source for tblITEMS.CustID, no
> >matter what I put for bound columns & number columns & column widths, it
[quoted text clipped - 3 lines]
> don't; combo boxes are vital on forms, but are a pernicious misfeature
> in Tables.

um, yes. OK.

And finally, MY NEW QUESTION:

I made a form called DATA ENTRY. But it gets very upset if I try to enter a
new Item Name, something that is not already listed in one of the INDEX
tables. The other fields accept new info, but not this one field. All my
tables have the same primary key, Item#, except INDEX briefs, because it's a
one-many-and-many-to-one relationship.

> >*Working as a temp in a company w/no trained Access users; limited in how
> >many hours I can study up on this. Have tried Access manual, Northwind &
[quoted text clipped - 5 lines]
>
> John W. Vinson[MVP]

My problem is that I take it too far in that direction, trying to make a
process really efficient instead of just getting it done the established way.
Unfortunately I'm not really paid to do that!

sigh,
Susan

Reply to this Message

John Vinson - 25 Jul 2005 07:22 GMT

>John, thanks for responding! Of course it took me a week to get back to you,
>sorry...
[quoted text clipped - 6 lines]
>thanks for the tip; haven't encountered warnings about them though. don't
>want to change it now i'm confused enough already.

Irrelevant.

Users should NEVER see table or query datasheets; they should see
Forms for onscreen use, and Reports for printing. On a Form or Report
you can use whatever caption you like for the controls on the form.
You're not limited to the fieldnames.

>> >I'm tracking our products for kosher status & proprietary status, using >Access 2003.
>> >Tbl Briefs: "Brief#"; "Item#"
[quoted text clipped - 20 lines]
>possible for TblBriefs). Had a hard time establishing the two "one-to-many"
>relationships for that "many-to-many" relationship.

Sounds like you're overdoing it. Don't split tables down to the
individual field level - split tables down to the individual ENTITY
level. A given Entity will usually have several "attributes"; each
attribute is a field. For example a Customer entity would have a
unique CustID, and then LastName, FirstName, MiddleName maybe, perhaps
some contact information - chunks of information that you need to know
about a customer, but chunks which are uniquely determined by the
CustID and don't depend on anything else. I really don't know what
other tables you are talking about using Item# as the Primary Key -
but one to one relationships, joining Item# Primary Key to Item#
Primary Key, are *very* rarely either necessary nor appropriate; you
can just use one table.

The criticism of "committing spreadsheet" arises when you put a
legitimate one-to-many relationship into each row, for instance having
a table of Items with fields Brief1, Brief2, Brief3 and Brief4. You'ld
need to take off your briefs if you had such a table...

>> >4) usually can only establish "indeterminate" relationships, whether I show
>> >tables & drag fields or Create New relationship
[quoted text clipped - 8 lines]
>primary index field but I guess I can't do that after the records are already
>entered?

Please describe this table. How can you (or the computer, either one)
uniquely identify which record is which? With no PK, there'd be no
protection against having two or three completely identical records;
how would you be able to link to these, or delete one?

>> >Problems: (after many many permutations)
>> >1) not permitted to delete CustID from tblITEMS, due to a relationship which
[quoted text clipped - 8 lines]
>made new ones. But when I tried to delete CustID from a table, that's when
>it would give me the relationships error.

I can post some VBA code to delete ALL relationships, visible or not,
if you need it.

>> >2) Form keeps asking for parameters every time I try to look at anything;
>>
[quoted text clipped - 36 lines]
>tables have the same primary key, Item#, except INDEX briefs, because it's a
>one-many-and-many-to-one relationship.

again... You have not posted any description of many tables with the
same primary key; and such tables are almost certainly overnormalized
and WRONG. If you have multiple (nonrepeating) pieces of discrete
information about an Item, by all means use ONE items table, with
fields for those things.

>> >*Working as a temp in a company w/no trained Access users; limited in how
>> >many hours I can study up on this. Have tried Access manual, Northwind &
[quoted text clipped - 9 lines]
>process really efficient instead of just getting it done the established way.
> Unfortunately I'm not really paid to do that!

Well, I wish I could help more.

John W. Vinson[MVP]

Reply to this Message