Relationship question...

  • Thread starter Thread starter ChrisBat
  • Start date Start date
C

ChrisBat

...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
 
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
________________________
 
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
 
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
________________________
 
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.
 
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
 
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.
 
Back
Top