multi - table woes

[potts]

I am using a query to build a report, but can't get it to select the records I want!!

In the query I have 3 tables:

tblCustomers
tblAthletes
tblSessionDetails

tblCustomers has a 1-many relationship with tblAthletes on a CustomerID field

tblAthletes has a 1-many relationship with tblSessionDetails on an AthleteID field

OK so far?

Now, tblSessionDetails also has a CustomerID field that is not joined to tblCustomers. This field is required as charges are sometimes to the individual athlete and sometimes to the overall group i.e. customer.

Confused yet?

For my report, I need to include:

tblSessionDetails - AthleteID, CustomerID, SessionDate, Charge

The report is going to be linked to another report by tblCustomers![CustomerID]

The problem is that I can't get the dynaset to display the required information. If both tblCustomers![CustomerID] and tblSessionDetails![CustomerID] are included, the results in the tblSessionDetails![CustomerID] column are ignored. I have tried renaming the tblSessionDetails![CustomerID] field, but with no success.

Any ideas?

 

[Fizzio]

I'm not sure you need the extra customerID field in the tblSessions as you can gain this information via the links back to athletes -> customers UNLESS you are storing a different cusomerID and charging them. I'm assuming you only charge the customer for that particular athlete though. Does this make sense?

 

[potts]

yes it makes sense. But the as I do not always want the charge reflected on an athlete, I kinda need the extra CustomerID - I think!

i.e.
the information may look like this:

Day 1
Group 1 charged £20

Day 2
Athlete 2 charged £40

As such there would be no athlete information in Day 1 to get the customerId from.

Maybe I'm missing the point?

 

[Fizzio]

I see what you are getting at but how do you decide who to charge?
Can a customer use the session without any athletes participating?
If so, you may need to expand your SessionTable to show who was chargeable for the session, Customer or Athlete to pull the relevant ID to charge to.
HTH

 

[potts]

never thought of doing it that way. The decision as to who is charged depends on the booking. The gym is used either as a group booking (Customer charged), or athletes come in on their own, which means that they are sometimes liable for certain elements of the charge - confusing and overly complicated I know, but that's what I've been left with. Oh the joys!!!

 

[Fizzio]

In that case, the athlete becomes a customer!
How about restructuring the tables to allow all athletes to be customers but in the customer table, add a field for customer type. You can then use a lookup table to identify if they are an athlete, coach, manager etc.

I'm not too sure about this but you could (in theory) create another table with for customer responsibilities eg

tblCustomerResponsibilities
ResponsibilityID
CustomerID (FK to CustomersID in Customers)
AthleteID (FK to CustomerID in Customers)

Just a couple more things to ponder over

Kristin

 

[potts]

I was being an ass!

I just realised that if I made the customer an athlete as well then it solved all the problems. Talk about making life difficult for yourself (and others - sorry!)

Thanks for the help and patience

 

[Fizzio]

No probs. It's always helpful to bounce ideas around and I've found this place the best to do it.