not updatable query

[babs]

I had two tables and everything worked fine. When I added the quotetable the
query is now not updateable- if I get rid of the clienttable in the query it
is fine but I need to grab the Client name from it since it is the only place
it is.See current table design below:

Client Table
ClientId(Primary key)
clientnumber
Clientname
address
city
state
etc.

Ticket Entry Table
ClientId(primary)
Ticket#(primary)
together these two are unique
quoteid
servicedate
comments
truck#
truckhours

(These above table fine in query until added below table)
Quotetable
quoteid(Primary)
todaysdate
Clientid
quotedate
job
etc.

I am sure it is something to do with the relationships. Probably have to
add a new field to ticketentry table (autonumber) for primary key. We like
the clientid and ticket# together as primary since want to full proof data
entry person from possibly duplicating this combinations. Any other way to
set code so these two fields can't have duplicate records(for these two
fields) - or do I even have to change the ticketentry table fields at all so
the query can work and be updateable.

Thanks,
Barb

 

[TC]

I had two tables and everything worked fine. When I added the quotetable the
query is now not updateable- if I get rid of the clienttable in the query it
is fine but I need to grab the Client name from it since it is the only place
it is.See current table design below:

Client Table
ClientId(Primary key)
clientnumber
Clientname
address
city
state
etc.

Looks fine.

Ticket Entry Table
ClientId(primary)
Ticket#(primary)
together these two are unique

Looks fine so far. I assume that several clients could have the same
Ticket#? So only the combination of clientid & ticket# is unique, not
just ticket#?

quoteid
servicedate
comments
truck#
truckhours

I assume there is at most one quote, and at most one truck, per client
ticket. (That is what those fields, being there, imply.)

So far we have a 1:m from client to ticket. You'ld need to have defined
& enforced that relationship.

Quotetable
quoteid(Primary)
todaysdate
Clientid
quotedate
job
etc.

So a client can have many quotes, and each quote has a unique quoteid,
regardless of client. The quoteid, on its own, is unique. There are no
two quotes with the same quoteid (regardless of client).

Here we have a 1:m from client to quote. You'd need to have defined &
enforced that relationship.


So - you have a 1:m from client to ticket, and another 1:m from client
to quote. That means there is a MANY-TO-MANY relationship between
ticket and quote. So a query joining those three tables, will not be
updatable.

Yes? No?

HTH,
TC [MVP Access]

 

[mnature]

You might try a form with a subform. For looking up information mainly by
ClientID, then the subform is where you look up information about the
Ticket#. For looking up information mainly by the Ticket#, then the subform
is where you look at information about the ClientID.

However, a question that comes to mind is whether the ClientID can be
duplicated in your Ticket Entry Table. If you have set the ClientID to be
unique in that table, then a client can only be listed once. You say that
the ClientID and Ticket# are unique together, but it would seem that you
could just have the Ticket# be unique, if you need a client to be able to buy
more than one ticket. Can two clients be on the same Ticket#?

 

[babs]

From BEFORE:
Looks fine so far. I assume that several clients could have the same
Ticket#? So only the combination of clientid & ticket# is unique, not
just ticket#?
YOU ARE EXACTLY CORRECT HERE.

I assume there is at most one quote, and at most one truck, per client
ticket. (That is what those fields, being there, imply.)

The quote table would happen first and there is not ticket # until the
client calls and orders material based on the quote. Then it is put into the
Ticket entry table based off of the quote info(not every ticket entry needs
to be based off of a quote) and this is where it gets the ticket# and yes
only one truck per client,ticket. that truck can be assigned later for
someone else.

All of the one to many relationships you mentioned below are established.

Biggest issue.

So - you have a 1:m from client to ticket, and another 1:m from client

to quote. That means there is a MANY-TO-MANY relationship between
ticket and quote. So a query joining those three tables, will not be
updatable.

YES!!!! Any way around it. My gut says I need to assign an autonumber Primary key for the ticket entry table. - get rid of two fields together as primary keys Would this help at all???? Also if I do this and it helps how can I set up data entry that the combo of client and ticket# is unique.

thanks for your help.

Need that client name info!!
Barb

I had two tables and everything worked fine. When I added the quotetable the
query is now not updateable- if I get rid of the clienttable in the query it
is fine but I need to grab the Client name from it since it is the only place
it is.See current table design below:

Client Table
ClientId(Primary key)
clientnumber
Clientname
address
city
state
etc.

Looks fine.

Ticket Entry Table
ClientId(primary)
Ticket#(primary)
together these two are unique

Looks fine so far. I assume that several clients could have the same
Ticket#? So only the combination of clientid & ticket# is unique, not
just ticket#?

quoteid
servicedate
comments
truck#
truckhours

I assume there is at most one quote, and at most one truck, per client
ticket. (That is what those fields, being there, imply.)

So far we have a 1:m from client to ticket. You'ld need to have defined
& enforced that relationship.

Quotetable
quoteid(Primary)
todaysdate
Clientid
quotedate
job
etc.

So a client can have many quotes, and each quote has a unique quoteid,
regardless of client. The quoteid, on its own, is unique. There are no
two quotes with the same quoteid (regardless of client).

Here we have a 1:m from client to quote. You'd need to have defined &
enforced that relationship.


So - you have a 1:m from client to ticket, and another 1:m from client
to quote. That means there is a MANY-TO-MANY relationship between
ticket and quote. So a query joining those three tables, will not be
updatable.

Yes? No?

HTH,
TC [MVP Access]

 

[babs]

From BEFORE:
Looks fine so far. I assume that several clients could have the same
Ticket#? So only the combination of clientid & ticket# is unique, not
just ticket#?
YOU ARE EXACTLY CORRECT HERE.

I assume there is at most one quote, and at most one truck, per client
ticket. (That is what those fields, being there, imply.)

The quote table would happen first and there is not ticket # until the
client calls and orders material based on the quote. Then it is put into the
Ticket entry table based off of the quote info(not every ticket entry needs
to be based off of a quote) and this is where it gets the ticket# and yes
only one truck per client,ticket. that truck can be assigned later for
someone else.

All of the one to many relationships you mentioned below are established.

Biggest issue.

So - you have a 1:m from client to ticket, and another 1:m from client

to quote. That means there is a MANY-TO-MANY relationship between
ticket and quote. So a query joining those three tables, will not be
updatable.

YES!!!! Any way around it. My gut says I need to assign an autonumber Primary key for the ticket entry table. - get rid of two fields together as primary keys Would this help at all???? Also if I do this and it helps how can I set up data entry that the combo of client and ticket# is unique.

thanks for your help.

Need that client name info!!
Barb

 

[TC]

I'm not quite clear what you want to achieve.

Can you describe, in plain language, what data you want the query to
return? Do not use /any/ technical terms like primary key or
autonumber. For example, "I want the query to return one row for each
ticked for each client", or somesuch.

HTH,
TC [MVP Access]

 

[Lynn Trapp]

TC [MVP Access]

This is really awesome to see... Congratulations, TC.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[babs]

Still haven't heard back any ideas???? See my 11:41 am posting.
Thanks,
Barb

I had two tables and everything worked fine. When I added the quotetable the
query is now not updateable- if I get rid of the clienttable in the query it
is fine but I need to grab the Client name from it since it is the only place
it is.See current table design below:

Client Table
ClientId(Primary key)
clientnumber
Clientname
address
city
state
etc.

Looks fine.

Ticket Entry Table
ClientId(primary)
Ticket#(primary)
together these two are unique

Looks fine so far. I assume that several clients could have the same
Ticket#? So only the combination of clientid & ticket# is unique, not
just ticket#?

quoteid
servicedate
comments
truck#
truckhours

I assume there is at most one quote, and at most one truck, per client
ticket. (That is what those fields, being there, imply.)

So far we have a 1:m from client to ticket. You'ld need to have defined
& enforced that relationship.

Quotetable
quoteid(Primary)
todaysdate
Clientid
quotedate
job
etc.

So a client can have many quotes, and each quote has a unique quoteid,
regardless of client. The quoteid, on its own, is unique. There are no
two quotes with the same quoteid (regardless of client).

Here we have a 1:m from client to quote. You'd need to have defined &
enforced that relationship.


So - you have a 1:m from client to ticket, and another 1:m from client
to quote. That means there is a MANY-TO-MANY relationship between
ticket and quote. So a query joining those three tables, will not be
updatable.

Yes? No?

HTH,
TC [MVP Access]

 

[TC]

Thanks Lynn, I do appreciate your congratulations

The process of getting it, in my case, was rather unusual! It's been
bubbling away for the last few months. But I resolved not to count any
chickens, until they had hatched. I got the confirmation a couple of
days ago, when all of the emails went out.

Looking forward to meeting you all, in the mvp groups. I'll also have a
website up soon, with some personal information, & my hints & tips for
developers

Cheers,
TC

 

[TC]

Thanks Lynn!

I replied to this a few minutes ago, but it seems to have disappeared
into the big round file!

I'll have a website up soon with some personal info. Looking forward to
meeting you all in the mvp newsgroups!

Cheers,
TC [MVP Access]

 

[TC]

Sorry babs, a few of my replies from last night seem to have
disappeared. I post using google, and google has had some weird
problems in the past few days. So let's try again!

I think the way to solve this easily, will be for you to describe what
data you need from the query, but without making any reference to the
tables, or their primary keys. Ie. do not us those terms at all, in
your description. Eg. "I need a query to return a row for each ticket,
for each person", or somesuch. Then we can raidly map that into your
table structure (which we now understand), and/or, suggest some changes
to that structure.

Sorry for the delay,
TC [MVP Access]

 

[Lynn Trapp]

Thanks Lynn, I do appreciate your congratulations

You're quite welcome

Looking forward to meeting you all, in the mvp groups.

Well, we are all waiting for you to make an appearance over there. Come on
in and introduce yourself to everyone

I'll also have a
website up soon, with some personal information, & my hints & tips for
developers

Cool! I can't wait to see it.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[babs]

TC,

I want the query to return 1 row for each ORDER- which is unique for each
clientid and ticket#(combined). The order can be based off of a quote (from
quote table) or just input the order with no previous quote made to the
customer- then no quote id.

Don't want to confuse you but here are the fields I would like in an
updatable query.

quoteid(drop-down) may or may not select
ClientId(if quoteid selected) it would fill this in- if not select dropdown
based off of Client table
ClientName
ticket#
Service date
truck#
truckhours
job
cost

(Client id,job, and cost could come from quotetable if selected a quoteid-
otherwise want user to put it in)
Not sure which table to pull the Client id from???-
I have things working how I want them but when add the client table in to
grab the client name- it becomes not updatable since many to many
relationship between the ticketentry table and quote table.

Any suggestions?????

Thanks for your help,
Barb