473,323 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Is there any good solution to make a middle table or relation that allows usto have for each record in the Client Two or more records in the category connected with it?

Hi5
Hi,
I am designing a database for a client in which It has a client table
including the followings:

1-Table Client [Row_ID, Client_name, Client_full name]

2-Table lookupcategory [row_ID category_name]

3-Table Ctegory [row_ID, category_ID, client_ID]

Is there any good solution to make a middle table or relation that
allows us to have for each record in the Client Two or more records in
the category connected with it?

I know it sound stupid and it maybe against all normalisation rules but
it is in the client'srequierments.

Many thanks

Hi5

Nov 13 '05 #1
15 2666
Hi5 wrote:
Hi,
I am designing a database for a client in which It has a client table
including the followings:

1-Table Client [Row_ID, Client_name, Client_full name]

2-Table lookupcategory [row_ID category_name]

3-Table Ctegory [row_ID, category_ID, client_ID]

Is there any good solution to make a middle table or relation that
allows us to have for each record in the Client Two or more records in
the category connected with it?

I know it sound stupid and it maybe against all normalisation rules but
it is in the client'srequierments.


"it sounds stupid" highly depends on the point of view.

I *am* surprised by the question upon first reading, because this looks
like pretty standard design to me. And fairly normalized too.

Table 3 will function as junction table, as it is often called. Be sure
to set relationships from tables 1 and 2 to table 3, using the correct
fields (I usually name those fields the same to indicate they store the
same piece of information. row_ID in Ctegory does not tell you it is a
category ID, whereas the corresponding field in table 3 is named after
its function. Anyway)

When you set the primary key of table 3 to contain both the categoryID
and clientID, you have ensured that any combination of these can occur
in the junction table. That means any client can have some category, and
a client can have more than one category at a time.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #2
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:d9**********@localhost.localdomain:
Hi5 wrote:
Hi,
I am designing a database for a client in which It has a client
table including the followings:

1-Table Client [Row_ID, Client_name, Client_full name]

2-Table lookupcategory [row_ID category_name]

3-Table Ctegory [row_ID, category_ID, client_ID]

Is there any good solution to make a middle table or relation
that allows us to have for each record in the Client Two or more
records in the category connected with it?

I know it sound stupid and it maybe against all normalisation
rules but it is in the client'srequierments.


"it sounds stupid" highly depends on the point of view.

I *am* surprised by the question upon first reading, because this
looks like pretty standard design to me. And fairly normalized
too.

Table 3 will function as junction table, as it is often called. Be
sure to set relationships from tables 1 and 2 to table 3, using
the correct fields (I usually name those fields the same to
indicate they store the same piece of information. row_ID in
Ctegory does not tell you it is a category ID, whereas the
corresponding field in table 3 is named after its function.
Anyway)

When you set the primary key of table 3 to contain both the
categoryID and clientID, you have ensured that any combination of
these can occur in the junction table. That means any client can
have some category, and
a client can have more than one category at a time.


All true, but doesn't have anything to do with the question as I
understand it.

The answer is that there really isn't any way in a Jet database to
prevent the deletion of the last two records. With a database engine
that has triggers you could certainly do it, but not in a Jet
database.

Of course, I don't quite see how you could ever do it except with
triggers. The first trigger would be on the main table, to insert
the two required records. The second trigger would be attached to
the delete action of the join table to insure that you couldn't
delete the last two records.

But any structural schema that requires two records to exist in the
join table is going to make it difficult to add records to the main
table, since the join table can't have the records until the main
record is created, but you've got a requirement that the main record
can't exist unless it has two required records in the join table.

In Access with a Jet back end, you're going to have to program it.

This is a perfect example of a situation that would be well-handled
by having a class module that does the add, because you'd be
encapsulating the addition of records to two tables in a class that
can be treated as an object. There's not much need for multiple
instances, but it certainly makes the process easy. And, of course,
internal to the class, you'd want the whole thing wrapped in a
transaction, so that if any part of it fails, the whole thing is
rolled back.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #3
David W. Fenton wrote:
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:d9**********@localhost.localdomain:

Hi5 [OP] wrote:
[snip]
Is there any good solution to make a middle table or relation
that allows us to have for each record in the Client Two or more
records in the category connected with it?

[snip]
All true, but doesn't have anything to do with the question as I
understand it.


I appreciate your explanation here, as it confirms my beliefs about more
complex record operations (object and transaction, great tools). I still
see both possibilities for the question, as 'allows' invokes in me the
'possibility'-story more than it does the 'requirement'-story. Am
waiting for the OP to shine some light.

I presume you took the 'two' keyword as leading in the question?

Please understand that I am still trying to learn the language :-) and
there may well be something that I'm missing.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #4
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:d9**********@localhost.localdomain:
David W. Fenton wrote:
Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:d9**********@localhost.localdomain:

Hi5 [OP] wrote:
[snip]
Is there any good solution to make a middle table or relation
that allows us to have for each record in the Client Two or more
records in the category connected with it?

[snip]
All true, but doesn't have anything to do with the question as I
understand it.


I appreciate your explanation here, as it confirms my beliefs
about more complex record operations (object and transaction,
great tools). I still see both possibilities for the question, as
'allows' invokes in me the 'possibility'-story more than it does
the 'requirement'-story. Am waiting for the OP to shine some
light.

I presume you took the 'two' keyword as leading in the question?


Well, the requirement is "two or more," which means to me that
there's a restriction where you don't want just 1 or 0 records. I
can't see any reason to word it that way.
Please understand that I am still trying to learn the language :-)
and there may well be something that I'm missing.


I'm still learning it, too. ;)

And so, it seems, is the original poster, since "allow 2 or more" is
rather ambiguous, since it implies (as I said above) that 1 or 0 is
not allowed, though it's not clear that this is a requirement.

If the poster really meant "allow 0 or more" then the question
doesn't make a lot of sense, as the data structure already works for
that. It's only if the OP wants some additional restrictions that
there arises a set of problems, the ones I described.

At least, that's the only way I can read the OP and make sense out
of it.

For one, it would be helpful if the question were in the *message*
and not just in the subject!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #5
"David W. Fenton" wrote
For one, it would be helpful if the
question were in the *message*
and not just in the subject!


And, for those who are relatively new in this newsgroup, there are other
good suggestions for effective use of newsgroups at
http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #6

Hi,

Many thanks for reading my message and trying to solve the

problem it seems to be ambigiuos therefore,let me clarify

the requierements.

Here is the tables structure:

1-Table Client [Tclient_ID, Client_name, Client_full name]

2-Table lookupcategory [Tlookupcat_ID category_name]

3-Table Ctegory [Tcat_ID, category_ID, client_ID]

I will then have a row of data in which one client has (let say) 6
categorie related to it(It may go up to all categories which exist and
are curently about 100).

To have a such data structured in MS access is there anyway of
developenig a Database?

I would be grateful to hear from you.

Kindest regards
Hi5

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #7
Hi 5 <fk***************@yahoo.com> wrote in
news:gn*************@news.uswest.net:
Many thanks for reading my message and trying to solve the

problem it seems to be ambigiuos therefore,let me clarify

the requierements.

Here is the tables structure:

1-Table Client [Tclient_ID, Client_name, Client_full name]

2-Table lookupcategory [Tlookupcat_ID category_name]

3-Table Ctegory [Tcat_ID, category_ID, client_ID]

I will then have a row of data in which one client has (let say) 6
categorie related to it(It may go up to all categories which exist
and are curently about 100).

To have a such data structured in MS access is there anyway of
developenig a Database?

I would be grateful to hear from you.


Your table 3 is the join table between the two tables, and, yes,
that's exactly the way to model this kind of relationship.

However, you don't actually need an Autonumber ID in your 3rd table,
and, in fact, you'd be better off without it, and just have the two
fields, category_ID and client_ID, be a compound Primary Key (which
implies a unique index). This is accomplished by highlighting both
fields in table design and clicking the KEY icon. This will set the
two fields as a compound primary key, and add a unique index on that
pair of fields.

You may also want to then add a non-unique index to the second
field.

Now, this means you will have 0 or more records in the join table.
There is no way with Access to put any more restrictions on that
table (such as limiting to 10 entries, or requiring at least 2).

If that's sufficient, your original proposal was already sufficient
to accomplish what you wanted, and that leaves me puzzled as to why
you asked a question about it, given that you'd already come to the
right answer on your own.

Perhaps there are other issues?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8
Hi5
Hi,
Thanks for the help, Yes there is one other issue that is the fact that
actual data comes from a very large excel sheet which was 100 columns
X 200000 rows, and the client wants to view the data in the same
format, upto here there is no problem, but it starts from where that
they have in single row 35 categories related to one client , and my
question is this is this possible with this design in MS access and if
yes how Please let me know of your opinion?

Many Thanks

Kindest regards

Nov 13 '05 #9
Hi5
Hi,
Thanks for the help, Yes there is one other issue that is the fact that
actual data comes from a very large excel sheet which was 100 columns
X 200000 rows, and the client wants to view the data in the same
format, upto here there is no problem, but it starts from where that
they have in single row 35 categories related to one client , and my
question is this is this possible with this design in MS access and if
yes how Please let me know of your opinion?

Many Thanks

Kindest regards

Nov 13 '05 #10
"Hi5" <fk***************@yahoo.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:
Thanks for the help, Yes there is one other issue that is the fact
that
actual data comes from a very large excel sheet which was 100
columns
X 200000 rows, and the client wants to view the data in the same
format, upto here there is no problem, but it starts from where
that they have in single row 35 categories related to one client ,
and my question is this is this possible with this design in MS
access and if yes how Please let me know of your opinion?


Yes, you can create a query with all 3 tables in it and drag all the
the fields in the two tables that are joined (i.e., omitting the
join table) into the QBE grid. The result will be something that
looks just like a spreadsheet where all the data in the main table
repeats.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11
Hi5
Hi,
If the structure is O.K and evry thing else is allright it doesn't
work?
I mean I have a client who has five categories how to record these in
the databes?
Many Thabks

Nov 13 '05 #12
Hi5
Hi,
I am new to access I used to do DBs in Oracle and Mysql,I would be
grateful if someone could translate the following words for me
What is QBE grid?
How to do that?
Many Thanks

Nov 13 '05 #13
> I am new to access I used to do DBs in Oracle and Mysql,I would be
grateful if someone could translate the following words for me
What is QBE grid?
Query By Example, the basic design view for a query.
How to do that?


Select a query object in the database window and click the button
Design, or choose the equivalent command from the menu. Or, press
Ctrl-Enter on the keyboard (my preference)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #14
"Hi5" <fk***************@yahoo.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:
If the structure is O.K and evry thing else is allright it doesn't
work?
I mean I have a client who has five categories how to record these
in the databes?


I don't know the answer to your question because you haven't given
any clues as to what, exactly, isn't working.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #15
Hi5
Dear Dave,

You are absolutely right, mate!

In this matter the only logical solution would be as you stated to use
and link an object class ,Or linked list to each field in the middle
table ,Anyway I could not find any logical solution in access so, I
used a stupid one which is to make the middle table as big as the most
number of data existed + a number wich will cover all possilities.

Many Thanks

Nov 13 '05 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

36
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...
5
by: yvan | last post by:
Approximately once a month, a client of ours sends us a bunch of comma-delimited text files which I have to clean up and then import into their MS SQL database. All last week, I was using a Cold...
3
by: deko | last post by:
I'm wondering if there is a better way to see if there are existing records... strRecCount = DCount("Cat_ID", "tblEntity", "=" & !!.Form!.Form!) If this evaluates to 0 then there are no...
0
by: Hi5 | last post by:
Hi, I am designing a database for a client in which It has a client table including the followings: 1-Table Client 2-Table lookupcategory 3-Table Ctegory
13
by: Shannan Casteel via AccessMonster.com | last post by:
I set up two tables (one with the regular claim info and another with ClaimNumber, PartNumber, and QuantityReplaced). The ClaimNumber is an autonumber and the primary key in both tables. I made a...
3
by: Strasser | last post by:
In a nested subform in datasheet view, an interviewer of homeless people picks a descriptive CATEGORY from 20 descriptive categories. The 20 categories are displayed via a combo box. (Categories...
5
by: Neil | last post by:
I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in an MDB file. The db is used by about 30 users on a LAN, and an additional 10 or so on a WAN. Recently, one of the WAN...
4
by: Clint Schaefer | last post by:
Apologies in advance. I've seen similar questions, but none of the solutions seem to be working for me... I have a form that allows a user to select mutiple items (payroll deduction codes) in a...
9
by: Miro | last post by:
VS2008 I have created 3 tables. Vendors Customers PhoneNumbers each have their own key Vendor has: VendorID - int unique identifier Customer has: CustomerID - int unique identifier
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.