PC Review


Reply
Thread Tools Rate Thread

Relationship question...

 
 
ChrisBat
Guest
Posts: n/a
 
      11th Nov 2003
...and no, I'm not referring to the communication style I have with my
wife! :-)
I'm trying to build a relationship between three tables. The first has
Billing Fields 1 - 10; a billing code can show up under any one of the
10 fields, and may show up multiple times. As well, a billing code may
or may not appear under a grouping title.
My problem is that I'm trying to establish a link between the Billing
Codes and the Billing Fields, and the Billing Groups with the Billing
Codes.
For example:

Table 1
Record 1: 889123 887123 886456 889001
Record 2: 887124 889123 889124
Record 3: 889003

Table 2
889123 | Replace Car muffler
889124 | Replace Windshield
887123 | Touch up scratch on paint
887124 | Repaint portion of car
887125 | Repaint entire car
886456 | Repair transmission
886457 | Repair engine block
889001 | Clean upholstery
889002 | Clean carpets
889003 | Clean windows

Table 3
889___ | Replace car part
887___ | Paint job on car
886___ | Engine repair (partial or complete)
88900_ | Clean interior and/or exterior of car

What I'm looking to do is build a relationship that will enable me to
translate the numbered billing codes to the Billing Description or to
the Group name. The problem I'm having is that when I try and link
Table 2 multiple times to Table 1, I get a message saying "A
relationship already exists. Do you want to edit the existing
relationship. To create a new relationship, click No."
I click No, and I continue on with my relationship building. When I
try and run a query or report based on these relationships, I get a
blank report or query - I think it's because Access is looking for
records that match ALL TEN criteria, not ANY or ALL of the criteria.
Any suggestions? I appreciate any and all advice.
Thanks,
Chris



------------------------------------------------
~~ Message posted from http://www.MSAccessForum.com/
~~ View and post usenet messages directly from http://www.MSAccessForum.com/

 
Reply With Quote
 
 
 
 
Gary Miller
Guest
Posts: n/a
 
      11th Nov 2003
Well, you will never have any success with the table design
you are using now. I strongly suggest you stop in your
tracks here and rethink your table designs.

Table 3, your task groups, is OK. Table 2 should have one
more field to hold the 'key' field of Table 3...

tblTasks
TaskID GroupID Task
889123 889 Replace Car muffler

Now GroupID is your 'relationship' or Foreign Key between
tblTasks and tblTaskGroups.

Your main table is where your real problems lay. Your are
storing tasks in 10 fields which will totally kill you when
you go to try and report or summarize what has been done.
Also, what happens when you get a big job that has 20 tasks?

You should break this up into two tables so that you will
have total flexibility on how many tasks are performed.

tblJob
JobID (Autonumber, Primary Key)
CustomerID (LongInteger to store the ID from a Customer
table)
JobDate (Date/Time) ' Don't know if you want a start and end
Date/Time???
' Any other fields you want that are specific to the job
such as Employee

tblJobTasks
JobTaskID (Autonumber, Primary Key)
TaskID (Foreign Key - Here is the relationship to the
tblTasks)

Now if you create a main form for the job, you would drop a
subform on it to allow you to put in one record for each
task performed, no more, no less.

Also, I would suggest changing the task group codes from
889___ to 889 so that you can keep them numeric if possible.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
"ChrisBat" <(E-Mail Removed)> wrote
in message news:(E-Mail Removed)...
> ..and no, I'm not referring to the communication style I

have with my
> wife! :-)
> I'm trying to build a relationship between three tables.

The first has
> Billing Fields 1 - 10; a billing code can show up under

any one of the
> 10 fields, and may show up multiple times. As well, a

billing code may
> or may not appear under a grouping title.
> My problem is that I'm trying to establish a link between

the Billing
> Codes and the Billing Fields, and the Billing Groups with

the Billing
> Codes.
> For example:
>
> Table 1
> Record 1: 889123 887123 886456 889001
> Record 2: 887124 889123 889124
> Record 3: 889003
>
> Table 2
> 889123 | Replace Car muffler
> 889124 | Replace Windshield
> 887123 | Touch up scratch on paint
> 887124 | Repaint portion of car
> 887125 | Repaint entire car
> 886456 | Repair transmission
> 886457 | Repair engine block
> 889001 | Clean upholstery
> 889002 | Clean carpets
> 889003 | Clean windows
>
> Table 3
> 889___ | Replace car part
> 887___ | Paint job on car
> 886___ | Engine repair (partial or complete)
> 88900_ | Clean interior and/or exterior of car
>
> What I'm looking to do is build a relationship that will

enable me to
> translate the numbered billing codes to the Billing

Description or to
> the Group name. The problem I'm having is that when I try

and link
> Table 2 multiple times to Table 1, I get a message saying

"A
> relationship already exists. Do you want to edit the

existing
> relationship. To create a new relationship, click No."
> I click No, and I continue on with my relationship

building. When I
> try and run a query or report based on these

relationships, I get a
> blank report or query - I think it's because Access is

looking for
> records that match ALL TEN criteria, not ANY or ALL of the

criteria.
> Any suggestions? I appreciate any and all advice.
> Thanks,
> Chris
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.MSAccessForum.com/
> ~~ View and post usenet messages directly from

http://www.MSAccessForum.com/
>



 
Reply With Quote
 
 
 
 
ChrisBat
Guest
Posts: n/a
 
      11th Nov 2003
Hi Gary,

Thanks for your response.
There are a few problems with your suggestion, given the situation I'
in (which I did not explain in my original post).
First - As far as the 10 fields go, this is not my doing. The databas
I'm exporting the data from is a DOS-based program that was develope
back in the mid-80's. I have been trying to get management to realis
the impediments caused by this, but no one around here either know
what I'm talking about or cares.
1 letter of instruction can have anywhere from 0 to 10 billing edit
tied to it. If there are going to be more than 10, we create a -
ticket off the original serial number. Each serial number i
completely unique, and will never be replicated (the first two digit
are the year, e.g. 23 for this year, followed by the 5 digit sequentia
number). There can be as many dash tickets as required in order to pa
the contractors.
Second - The groupings, again, are not my doing, but rather the manage
who designed the databases. In the example above, I went too simple
the first three digits are actually not in any way correlated with th
Billing Edit. The billing edits have been set up to be sequential, s
if we've added 15 codes this year, they will just fall into place
regardless of what they are going to be used for. I apologise for th
confusion - I didn't want to complicate my post, but ended up causin
more problems...
Is this a pain in the a**? Yes. There are a group of us here that ar
trying to break through this problem for different analytical purposes
and as I have registered with this site, I opted to post our problem.

I appreciate your assistance.
Chri


-----------------------------------------------
~~ Message posted from http://www.MSAccessForum.com
~~ View and post usenet messages directly from http://www.MSAccessForum.com

 
Reply With Quote
 
Gary Miller
Guest
Posts: n/a
 
      11th Nov 2003
Chris,

Don't envy you here. On to your relationship problem, you
say this is data that you are importing into Access that you
are trying to do analysis on. You may never be able to
create true relationships from what I see of the format. If
this is not realtime data, how about creating a routine to
'normalize' it from it's 'unnormalized' state?

If you agree with the table structures that I posted earlier
being a solution to the problem, I would consider writing a
routine to loop through your main table and break it up into
the two tables...
*********
tblJob
JobID (Autonumber, Primary Key)
CustomerID (LongInteger to store the ID from a Customer
table)
JobDate (Date/Time) ' Don't know if you want a start and end
Date/Time???
' Any other fields you want that are specific to the job
such as Employee

tblJobTasks
JobTaskID (Autonumber, Primary Key)
TaskID (Foreign Key - Here is the relationship to the
tblTasks)
*******

.... that I referred to earlier. You could pull the JobID,
date etc from the table and poke it into tblJob and then
check fields 1 - 10 to see if they have data and if so
create a new record for tblTasks with the Task# and the
JobID. Ex:

Your data as is...
Record 1: 889123 887123 886456 889001

would populate the second table
Record 1 889123
Record 1 887123
Record 1 886456
Record 1 889001
Record 2 887123
...etc...

Write the code once and you can just reprocess again any
time you reimport. Now you can do your reporting and
analysis on something that you can get your teeth into.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
"ChrisBat" <(E-Mail Removed)> wrote
in message news:(E-Mail Removed)...
> Hi Gary,
>
> Thanks for your response.
> There are a few problems with your suggestion, given the

situation I'm
> in (which I did not explain in my original post).
> First - As far as the 10 fields go, this is not my doing.

The database
> I'm exporting the data from is a DOS-based program that

was developed
> back in the mid-80's. I have been trying to get

management to realise
> the impediments caused by this, but no one around here

either knows
> what I'm talking about or cares.
> 1 letter of instruction can have anywhere from 0 to 10

billing edits
> tied to it. If there are going to be more than 10, we

create a -1
> ticket off the original serial number. Each serial number

is
> completely unique, and will never be replicated (the first

two digits
> are the year, e.g. 23 for this year, followed by the 5

digit sequential
> number). There can be as many dash tickets as required in

order to pay
> the contractors.
> Second - The groupings, again, are not my doing, but

rather the manager
> who designed the databases. In the example above, I went

too simple -
> the first three digits are actually not in any way

correlated with the
> Billing Edit. The billing edits have been set up to be

sequential, so
> if we've added 15 codes this year, they will just fall

into place,
> regardless of what they are going to be used for. I

apologise for the
> confusion - I didn't want to complicate my post, but ended

up causing
> more problems...
> Is this a pain in the a**? Yes. There are a group of us

here that are
> trying to break through this problem for different

analytical purposes,
> and as I have registered with this site, I opted to post

our problem.
>
> I appreciate your assistance.
> Chris
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.MSAccessForum.com/
> ~~ View and post usenet messages directly from

http://www.MSAccessForum.com/
>



 
Reply With Quote
 
Roger Carlson
Guest
Posts: n/a
 
      11th Nov 2003
The simple fact is that you CANNOT create relationships in the way that you
want to. Having multiple columns that store the same type of data violates
the most fundamental rule of relational database design--the First Normal
form. If you won't adhere to that, don't be surprised if your database
can't do things that a relational database should, because it isn't.

Gary is correct and his solution (or something very like it) is the only one
available. OK, so you don't have the ability to fix the source, but you CAN
import this non-normalized data into a normalized structure.

Just because something has always been that way does not mean it should
always stay that way.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"ChrisBat" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Gary,
>
> Thanks for your response.
> There are a few problems with your suggestion, given the situation I'm
> in (which I did not explain in my original post).
> First - As far as the 10 fields go, this is not my doing. The database
> I'm exporting the data from is a DOS-based program that was developed
> back in the mid-80's. I have been trying to get management to realise
> the impediments caused by this, but no one around here either knows
> what I'm talking about or cares.
> 1 letter of instruction can have anywhere from 0 to 10 billing edits
> tied to it. If there are going to be more than 10, we create a -1
> ticket off the original serial number. Each serial number is
> completely unique, and will never be replicated (the first two digits
> are the year, e.g. 23 for this year, followed by the 5 digit sequential
> number). There can be as many dash tickets as required in order to pay
> the contractors.
> Second - The groupings, again, are not my doing, but rather the manager
> who designed the databases. In the example above, I went too simple -
> the first three digits are actually not in any way correlated with the
> Billing Edit. The billing edits have been set up to be sequential, so
> if we've added 15 codes this year, they will just fall into place,
> regardless of what they are going to be used for. I apologise for the
> confusion - I didn't want to complicate my post, but ended up causing
> more problems...
> Is this a pain in the a**? Yes. There are a group of us here that are
> trying to break through this problem for different analytical purposes,
> and as I have registered with this site, I opted to post our problem.
>
> I appreciate your assistance.
> Chris
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.MSAccessForum.com/
> ~~ View and post usenet messages directly from

http://www.MSAccessForum.com/
>



 
Reply With Quote
 
ChrisBat
Guest
Posts: n/a
 
      11th Nov 2003
Thank you Gentleman.
It's 12 minutes till hometime for me - I'll pick this up tomorrow.
And no, I don't envy me either! :-)
Appreciate all your help.
Chri


-----------------------------------------------
~~ Message posted from http://www.MSAccessForum.com
~~ View and post usenet messages directly from http://www.MSAccessForum.com

 
Reply With Quote
 
Albert D. Kallal
Guest
Posts: n/a
 
      12th Nov 2003
While you can't make the relationships, you can MOST certainly make a query,
and drop in the table 10 times, and use left joins. So, you can have each of
the 10 fields return values from the other table.

This will at least give you lookups to the other tables.

Note the best, but the query builder will do this form you, and then you can
use this for reports.

So, fire up the query builder, and start dropping in the table 2. Draw a
join line from field to the table 2 (double click on the join line, and
select the option that says join on any table1, but not necessary a value
in table two). You can continue this process for 10 times, and thus you will
have a query that lets you display the other values. (you will have to
repeatedly drop in table 2 for each field).

So, you can't define relationships to do this, but you can certany build
querys that pulls the data togther for you.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.attcanada.net/~kallal.msn


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
table relationship in query & relationship mode =?Utf-8?B?UmxhdGlvbnNoaXA=?= Microsoft Access 1 3rd Oct 2005 04:07 PM
Relationship not in relationship window - I can't update table =?Utf-8?B?QU1MUg==?= Microsoft Access Database Table Design 3 28th Apr 2005 01:03 PM
Re: Relationship not showing up in relationship view Allen Browne Microsoft Access Database Table Design 1 10th Aug 2004 04:28 PM
Relationship not showing up in relationship view Bob Microsoft Access Database Table Design 1 10th Aug 2004 12:47 PM
Re: Relationship not showing up in relationship view tina Microsoft Access Database Table Design 2 10th Aug 2004 04:41 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:54 PM.