Hello.

This is probably quite simple but I have ODed on databases the last couple of days and I can no longer think straight!

So I have clients, and the clients attend sessions, two tables, many to many I guess. When a new session is created, the session number increments. However, I would like each client's session to have an identifier to show which session number that individual is on.

If I understand correctly, I need an intermediate table called clientsession. Is this called resolving? And if so, what are the various PKs and FKs?

Many thanks

Gerard

If I understand correctly, it does not sound like you need a Many to Many.

One client can attend many sessions (one to many).

One session can have many clients (one to many).

Have a portal in the clients table that shows the sessions for that client.

And have a portal in the sessions table that shows the clients for that session.

Does this make sense, or have I completely misunderstood your question?

Thanks David

No I'm not sure if I've explained correctly.

I have a table for each client showing all their sessions.

I want each session to be numbered sequentially per client. Currently with each new session created (for any given client), the session_id increments, so client A might have sessions 1, 3, 8 etc. I would like sessions to be numbered like this

client A session 1 (session_id 1)

client B session 1 (session_id 2)

client C session 1 (session_id 3)

client B session 2 (session_id 4)

client A session 2 (session_id 5)

client B session 3 (session_id 6)

I know it should be simple but I still get slightly confused with keys and joins. Is a join required to allocate individuals session_ids?

cheers

Gerard

If I understand correctly, your problem can be resolved very simply by going into Layout mode and typing @@ in the portal showing a client's sessions.

Or you could count a client's session by defining a calculation field in the Clients table = Count ( Sessions::session_id ).

Unless there can be more than one client per session, this is not a many-to-many situation. In any case, it's best not to monkey around with the ID numbers. There's no need to even show them in the portal.

Are you viewing the session in a portal (I presume)?

Do you want the sessions numbered sequentially from top to bottom, i.e., portal row 1 is always session 1, row 5 is always session 5, etc., or wiil some be random?

Thanks for the replies, I think the calculation field is a good idea.

David, I do want the sessions numbered sequentially (which should also be in date order) descending.

Cheers

Gerard