Update or Append Query help

[Adam]

Hi there,

Hoping someone can help.

Here is what I need to do.

I want to use a query to update one field with the text from another field
from the query with criteria.

It may work something like this:

(SELECT [PurchaseOrderLineId] FROM [qryPartWeightAssign] WHERE [Assign] FROM
[dbTicketLineItems] = -1) AND [PartNumberID] FROM [dbPurchaseOrderLineItems]
= [PartNoID] FROM [dbTicketLineItems]

Then I want to append or update it to the proper table [dbTicketLineItems].

I am a complete beginner at sql and doing update queries or append queries.
Please let me know if anyone can help.

 

[Jeff Boyce]

Adam

Please allow me to question the need. Why do you believe it is necessary to
replicate data in more than one field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Adam]

Here's the breakdown. I have a table with ticket numbers that I need to
assign or match to certain LineID's in another table. In my form I have
Customer and PO at the top, PO Details in the middle in a subform, and below
that, in another subform, Tickets with the same parts #'s as the PO's in the
middle form.

I needed to match those tickets with the same part number to certain line
items in a purchase order in order to complete the order.

This might not make any sense, and I may be going about it the wrong way,
but it works except I can't seem to find an easy way to link them. Ideally
I wanted to be able to click a check box next to the ticket line item that
would automatically assign the PO line to it. I am working on a macro to
accomplish this, so I was going to have the macro run an append or update
query that changes the text in the right fields and updates it in the table.

God, I'm confusing myself now.

Thanks for your help.

Adam

Please allow me to question the need. Why do you believe it is necessary
to replicate data in more than one field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there,

Hoping someone can help.

Here is what I need to do.

I want to use a query to update one field with the text from another
field from the query with criteria.

It may work something like this:

(SELECT [PurchaseOrderLineId] FROM [qryPartWeightAssign] WHERE [Assign]
FROM [dbTicketLineItems] = -1) AND [PartNumberID] FROM
[dbPurchaseOrderLineItems] = [PartNoID] FROM [dbTicketLineItems]

Then I want to append or update it to the proper table
[dbTicketLineItems].

I am a complete beginner at sql and doing update queries or append
queries. Please let me know if anyone can help.

 

[Jeff Boyce]

Adam

You've described "how" you are trying to do something, but I'm still a bit
fuzzy on the "what" ...

Are you saying that you want some way to connect part numbers to line items
in an order?

If so, this sounds a little like the example Order/OrderDetail in the
Northwind db that accompanies Access. If you are saying that you have a
customer (and a PO/Order number), and you have line items (Order Details)
that (?each) need to hold a single part#, I can see a main-form/subform set
up to do this. The OrderDetail table would include a field for part#, and
the OrderDetail subform would use a combo box to allow selection of the
part# that OrderDetail row represents.

Or am I not seeing clearly...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here's the breakdown. I have a table with ticket numbers that I need to
assign or match to certain LineID's in another table. In my form I have
Customer and PO at the top, PO Details in the middle in a subform, and
below that, in another subform, Tickets with the same parts #'s as the
PO's in the middle form.

I needed to match those tickets with the same part number to certain line
items in a purchase order in order to complete the order.

This might not make any sense, and I may be going about it the wrong way,
but it works except I can't seem to find an easy way to link them.
Ideally I wanted to be able to click a check box next to the ticket line
item that would automatically assign the PO line to it. I am working on a
macro to accomplish this, so I was going to have the macro run an append
or update query that changes the text in the right fields and updates it
in the table.

God, I'm confusing myself now.

Thanks for your help.

Adam

Please allow me to question the need. Why do you believe it is necessary
to replicate data in more than one field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there,

Hoping someone can help.

Here is what I need to do.

I want to use a query to update one field with the text from another
field from the query with criteria.

It may work something like this:

(SELECT [PurchaseOrderLineId] FROM [qryPartWeightAssign] WHERE [Assign]
FROM [dbTicketLineItems] = -1) AND [PartNumberID] FROM
[dbPurchaseOrderLineItems] = [PartNoID] FROM [dbTicketLineItems]

Then I want to append or update it to the proper table
[dbTicketLineItems].

I am a complete beginner at sql and doing update queries or append
queries. Please let me know if anyone can help.

 

[Adam]

Actually you are close.

I have purchase orders from customers and manifests with parts on them from
suppliers.

I have set up the following structure to my database:

Customer - Purchase Order - Purchase Order Line Items

Line items have WeightOrdered field and Part Number
and

Supplier - Manifest - Ticket Line Items

Ticket Line Items have NetWeight and PartNumber

It may take up to 4 or 5 Ticket Line Items to reach the WeightOrdered from
the Purchase Order.

I need to be able to assign multiple Tickets to the Purchase Order to
fulfill each Line item on it.

Does this help you understand what I am trying to do?

Adam

You've described "how" you are trying to do something, but I'm still a bit
fuzzy on the "what" ...

Are you saying that you want some way to connect part numbers to line
items in an order?

If so, this sounds a little like the example Order/OrderDetail in the
Northwind db that accompanies Access. If you are saying that you have a
customer (and a PO/Order number), and you have line items (Order Details)
that (?each) need to hold a single part#, I can see a main-form/subform
set up to do this. The OrderDetail table would include a field for part#,
and the OrderDetail subform would use a combo box to allow selection of
the part# that OrderDetail row represents.

Or am I not seeing clearly...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here's the breakdown. I have a table with ticket numbers that I need to
assign or match to certain LineID's in another table. In my form I have
Customer and PO at the top, PO Details in the middle in a subform, and
below that, in another subform, Tickets with the same parts #'s as the
PO's in the middle form.

I needed to match those tickets with the same part number to certain line
items in a purchase order in order to complete the order.

This might not make any sense, and I may be going about it the wrong way,
but it works except I can't seem to find an easy way to link them.
Ideally I wanted to be able to click a check box next to the ticket line
item that would automatically assign the PO line to it. I am working on
a macro to accomplish this, so I was going to have the macro run an
append or update query that changes the text in the right fields and
updates it in the table.

God, I'm confusing myself now.

Thanks for your help.

Adam

Please allow me to question the need. Why do you believe it is
necessary to replicate data in more than one field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there,

Hoping someone can help.

Here is what I need to do.

I want to use a query to update one field with the text from another
field from the query with criteria.

It may work something like this:

(SELECT [PurchaseOrderLineId] FROM [qryPartWeightAssign] WHERE [Assign]
FROM [dbTicketLineItems] = -1) AND [PartNumberID] FROM
[dbPurchaseOrderLineItems] = [PartNoID] FROM [dbTicketLineItems]

Then I want to append or update it to the proper table
[dbTicketLineItems].

I am a complete beginner at sql and doing update queries or append
queries. Please let me know if anyone can help.

 

[Adam]

I should say, if you are confused that we are a plating company, and we get
material in that is intended for a certain customer, ordered for that
customer. We simply plate it and send it on to the customer after it's
plated. This is why the Manifest and the Purchase Order are related, but
not easily connected except by part number, because the weight of the
bundles that come from the supplier don't precisely match the weight ordered
on the purchase order.

Adam

You've described "how" you are trying to do something, but I'm still a bit
fuzzy on the "what" ...

Are you saying that you want some way to connect part numbers to line
items in an order?

If so, this sounds a little like the example Order/OrderDetail in the
Northwind db that accompanies Access. If you are saying that you have a
customer (and a PO/Order number), and you have line items (Order Details)
that (?each) need to hold a single part#, I can see a main-form/subform
set up to do this. The OrderDetail table would include a field for part#,
and the OrderDetail subform would use a combo box to allow selection of
the part# that OrderDetail row represents.

Or am I not seeing clearly...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here's the breakdown. I have a table with ticket numbers that I need to
assign or match to certain LineID's in another table. In my form I have
Customer and PO at the top, PO Details in the middle in a subform, and
below that, in another subform, Tickets with the same parts #'s as the
PO's in the middle form.

I needed to match those tickets with the same part number to certain line
items in a purchase order in order to complete the order.

This might not make any sense, and I may be going about it the wrong way,
but it works except I can't seem to find an easy way to link them.
Ideally I wanted to be able to click a check box next to the ticket line
item that would automatically assign the PO line to it. I am working on
a macro to accomplish this, so I was going to have the macro run an
append or update query that changes the text in the right fields and
updates it in the table.

God, I'm confusing myself now.

Thanks for your help.

Adam

Please allow me to question the need. Why do you believe it is
necessary to replicate data in more than one field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there,

Hoping someone can help.

Here is what I need to do.

I want to use a query to update one field with the text from another
field from the query with criteria.

It may work something like this:

(SELECT [PurchaseOrderLineId] FROM [qryPartWeightAssign] WHERE [Assign]
FROM [dbTicketLineItems] = -1) AND [PartNumberID] FROM
[dbPurchaseOrderLineItems] = [PartNoID] FROM [dbTicketLineItems]

Then I want to append or update it to the proper table
[dbTicketLineItems].

I am a complete beginner at sql and doing update queries or append
queries. Please let me know if anyone can help.

 

[Jeff Boyce]

Adam

So you're saying that you need to associate one or more "Ticket Line Item"
rows with a "Purchase Order Line Item" row. That sounds like a one-to-many
relationship.

Do you have a table structure to handle this?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Actually you are close.

I have purchase orders from customers and manifests with parts on them
from suppliers.

I have set up the following structure to my database:

Customer - Purchase Order - Purchase Order Line Items

Line items have WeightOrdered field and Part Number
and

Supplier - Manifest - Ticket Line Items

Ticket Line Items have NetWeight and PartNumber

It may take up to 4 or 5 Ticket Line Items to reach the WeightOrdered from
the Purchase Order.

I need to be able to assign multiple Tickets to the Purchase Order to
fulfill each Line item on it.

Does this help you understand what I am trying to do?

Adam

You've described "how" you are trying to do something, but I'm still a
bit fuzzy on the "what" ...

Are you saying that you want some way to connect part numbers to line
items in an order?

If so, this sounds a little like the example Order/OrderDetail in the
Northwind db that accompanies Access. If you are saying that you have a
customer (and a PO/Order number), and you have line items (Order Details)
that (?each) need to hold a single part#, I can see a main-form/subform
set up to do this. The OrderDetail table would include a field for
part#, and the OrderDetail subform would use a combo box to allow
selection of the part# that OrderDetail row represents.

Or am I not seeing clearly...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here's the breakdown. I have a table with ticket numbers that I need to
assign or match to certain LineID's in another table. In my form I have
Customer and PO at the top, PO Details in the middle in a subform, and
below that, in another subform, Tickets with the same parts #'s as the
PO's in the middle form.

I needed to match those tickets with the same part number to certain
line items in a purchase order in order to complete the order.

This might not make any sense, and I may be going about it the wrong
way, but it works except I can't seem to find an easy way to link them.
Ideally I wanted to be able to click a check box next to the ticket line
item that would automatically assign the PO line to it. I am working on
a macro to accomplish this, so I was going to have the macro run an
append or update query that changes the text in the right fields and
updates it in the table.

God, I'm confusing myself now.

Thanks for your help.
Adam

Please allow me to question the need. Why do you believe it is
necessary to replicate data in more than one field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there,

Hoping someone can help.

Here is what I need to do.

I want to use a query to update one field with the text from another
field from the query with criteria.

It may work something like this:

(SELECT [PurchaseOrderLineId] FROM [qryPartWeightAssign] WHERE
[Assign] FROM [dbTicketLineItems] = -1) AND [PartNumberID] FROM
[dbPurchaseOrderLineItems] = [PartNoID] FROM [dbTicketLineItems]

Then I want to append or update it to the proper table
[dbTicketLineItems].

I am a complete beginner at sql and doing update queries or append
queries. Please let me know if anyone can help.

 

[Jeff Boyce]

Adam

So you're saying that you need to associate one or more "Ticket Line Item"
rows with a "Purchase Order Line Item" row. That sounds like a one-to-many
relationship.

Do you have a table structure to handle this?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Actually you are close.

I have purchase orders from customers and manifests with parts on them
from suppliers.

I have set up the following structure to my database:

Customer - Purchase Order - Purchase Order Line Items

Line items have WeightOrdered field and Part Number
and

Supplier - Manifest - Ticket Line Items

Ticket Line Items have NetWeight and PartNumber

It may take up to 4 or 5 Ticket Line Items to reach the WeightOrdered from
the Purchase Order.

I need to be able to assign multiple Tickets to the Purchase Order to
fulfill each Line item on it.

Does this help you understand what I am trying to do?

Adam

You've described "how" you are trying to do something, but I'm still a
bit fuzzy on the "what" ...

Are you saying that you want some way to connect part numbers to line
items in an order?

If so, this sounds a little like the example Order/OrderDetail in the
Northwind db that accompanies Access. If you are saying that you have a
customer (and a PO/Order number), and you have line items (Order Details)
that (?each) need to hold a single part#, I can see a main-form/subform
set up to do this. The OrderDetail table would include a field for
part#, and the OrderDetail subform would use a combo box to allow
selection of the part# that OrderDetail row represents.

Or am I not seeing clearly...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here's the breakdown. I have a table with ticket numbers that I need to
assign or match to certain LineID's in another table. In my form I have
Customer and PO at the top, PO Details in the middle in a subform, and
below that, in another subform, Tickets with the same parts #'s as the
PO's in the middle form.

I needed to match those tickets with the same part number to certain
line items in a purchase order in order to complete the order.

This might not make any sense, and I may be going about it the wrong
way, but it works except I can't seem to find an easy way to link them.
Ideally I wanted to be able to click a check box next to the ticket line
item that would automatically assign the PO line to it. I am working on
a macro to accomplish this, so I was going to have the macro run an
append or update query that changes the text in the right fields and
updates it in the table.

God, I'm confusing myself now.

Thanks for your help.
Adam

Please allow me to question the need. Why do you believe it is
necessary to replicate data in more than one field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there,

Hoping someone can help.

Here is what I need to do.

I want to use a query to update one field with the text from another
field from the query with criteria.

It may work something like this:

(SELECT [PurchaseOrderLineId] FROM [qryPartWeightAssign] WHERE
[Assign] FROM [dbTicketLineItems] = -1) AND [PartNumberID] FROM
[dbPurchaseOrderLineItems] = [PartNoID] FROM [dbTicketLineItems]

Then I want to append or update it to the proper table
[dbTicketLineItems].

I am a complete beginner at sql and doing update queries or append
queries. Please let me know if anyone can help.