3 Tables and query not updateable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

babs - 12 Jan 2006 15:37 GMT

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.

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

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
cost

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

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,
Barb

Reply to this Message

SteveS - 12 Jan 2006 17:50 GMT

> 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
[quoted text clipped - 62 lines]
> Thanks,
> Barb

Barb,

Idea 1:

If only one quote is allowed for one ticket, can you merge the ticket and
quote tables, adding a field (change quoteid) to a field that selects either
"Quote" or "Ticket"?

Then you are back to two tables and should be able to make an updatable query.

Idea 2:

The form has the client info and the query with ticket info and quotes is in
a (linked)subForm.

Idea 3 (similar to Idea 2 but no subform):

1) In the form HEADER, add a combo box with the RowSource as:

"SELECT ClientId, clientnumber, Clientname, address, city, state FROM
ClientTable ORDER BY ClientName"

On the Format Tab, set:

Column Count = 6
Column Widths = 0;0;1;0;0;0

change to match the number of fields and the width of the name field.

Name the combo box cboClient.

Add *unbound* text boxes for the client info: clientnumber,
address,city,state,zip, etc.

txtclientnumber
txtaddress
txtcity
.
.
txtzip

2) The query for the Form should not have any fields from the client table
and look something like:

SELECT ClientId, [ticket#] , quoteid, [Service date], truck#, truckhours,
job, cost FROM TicketTable LEFT JOIN Quotetable ON TicketTable.quoteid =
Quotetable.Quotetable WHERE [ticket#] = Forms!formName.cboClient ORDER BY
[Service date]

(Note the WHERE clause!!)

3) In the client combo box in the header, add the following to the after
update event :

Private Sub cboClient_AfterUpdate()

Me.txtclientnumber = Me.cboclient.column(1)
Me.txtclientnane = Me.cboclient.column(2)
Me.txtaddress = Me.cboclient.column(3)
Me.txtcity = Me.cboclient.column(4)

Me.Requery

End Sub

NOTE: air code....

HTH

Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Reply to this Message