Help please with Update Query

J

jwr

I am using Access 2003 and Windows XP Pro.

My database is sales oriented.

On my form, the following information is contained:
Order ID
Order Date
Customer ID
Payment Date
Payment $
Essett #
Essett Date

as well as much more.

My question is this. When the customer pays me, I bring the form up, and
enter the payment date and amount. Weekly, I must then forward that payment
on. I must enter one more number and date at the time of forwarding the
money. That number is called Essett (number for that particular
transmission - unique to that particular one). There may be 1 or 50 on the
transmission. The number varies depending upon payments received during the
week.

Is there a way to create a query in which I enter the "Essett" number only 1
time and Date and have it update the form that contains my payment
information? Or must I go back into that form weekly, call up each order ID
being paid, and enter the Essett number and date?

Thank you in advance.
 
O

OfficeDev18 via AccessMonster.com

I would hide the whole thing behind a command button.

If essett dates are always Monday, for example, or any particular day, figure
out (in VBA code) the date for starting your data update. Then write an
Update query, such as:

"UPDATE tblSalesData SET [Essett #]=[Enter Essett Number], [Essett Date] = #"
& Date() & "# WHERE [Payment Date] Between #" & ResultOfPreviousComputation &
"# And DateAdd("d",6,ResultOfPreviousComputation) & "#;"

This will do all the dirty work for you.

By the way, the reason I have the SQL prompting you to enter an Essett number
is only because of my ignorance of your database's capabilities. If you can
compute the essett number automatically, by all means hard code that
information into the SQL.

HTH,
Sam
 
J

jwr

Thank you for your reply. I think I am lost in your reply. I know that you
are wanting me to input my information. May I quiz you more?

The essett date is always Wednesday.

Since I am completely ignorant regarding update queries, please tell me
where I need to enter my particular information. It appears that
tblSalesData should be my "Order Details Table" (table from which form is
created). I would enter my essett number and essett date.


I am lost on this portion --
"# WHERE [Payment Date] Between #" & ResultOfPreviousComputation &
"# And DateAdd("d",6,ResultOfPreviousComputation) & "#;"

Please excuse my ignorance. I greatly appreciate your assistance.

JR




OfficeDev18 via AccessMonster.com said:
I would hide the whole thing behind a command button.

If essett dates are always Monday, for example, or any particular day, figure
out (in VBA code) the date for starting your data update. Then write an
Update query, such as:

"UPDATE tblSalesData SET [Essett #]=[Enter Essett Number], [Essett Date] = #"
& Date() & "# WHERE [Payment Date] Between #" & ResultOfPreviousComputation &
"# And DateAdd("d",6,ResultOfPreviousComputation) & "#;"

This will do all the dirty work for you.

By the way, the reason I have the SQL prompting you to enter an Essett number
is only because of my ignorance of your database's capabilities. If you can
compute the essett number automatically, by all means hard code that
information into the SQL.

HTH,
Sam
I am using Access 2003 and Windows XP Pro.

My database is sales oriented.

On my form, the following information is contained:
Order ID
Order Date
Customer ID
Payment Date
Payment $
Essett #
Essett Date

as well as much more.

My question is this. When the customer pays me, I bring the form up, and
enter the payment date and amount. Weekly, I must then forward that payment
on. I must enter one more number and date at the time of forwarding the
money. That number is called Essett (number for that particular
transmission - unique to that particular one). There may be 1 or 50 on the
transmission. The number varies depending upon payments received during the
week.

Is there a way to create a query in which I enter the "Essett" number only 1
time and Date and have it update the form that contains my payment
information? Or must I go back into that form weekly, call up each order ID
being paid, and enter the Essett number and date?

Thank you in advance.
 
O

OfficeDev18 via AccessMonster.com

I will try to answer as well as play catchup on the information I didn't say
explicitly before.

Create a command button on your form, maybe call it cmdEnterEssett. In the
OnClick event of your new command button, do the following two related tasks:
1-calculate the start date to enter essett date, and 2-go ahead and modify
all appropriate records with the correct information.

Since essett day is Wednesday, I'm assuming the records to be updated are
dated last week Wednesday through Tuesday (yesterday). Therefore, your
OnClick event in VBA might look something like

Private sub cmdEnterEssett_OnClick()

Dim StartDate As Date

StartDate = DateAdd("d",-7,Date()) 'this gives last week Wednesday

DoCmd.RunSQL "UPDATE tblSalesData SET [Essett #]=[Enter Essett Number],
[Essett Date] = #" _
& Date() & "# WHERE [Payment Date] Between #" & StartDate & _
"# And DateAdd("d",6,StartDate) & "#;"

End Sub

Again, you will be asked by the query to enter an essett number each time you
run the query.

By the way, the underscores at the end of each line of SQL is the
continuation character, which tells Access that the statement carries over to
the next line.

Sam
Thank you for your reply. I think I am lost in your reply. I know that you
are wanting me to input my information. May I quiz you more?

The essett date is always Wednesday.

Since I am completely ignorant regarding update queries, please tell me
where I need to enter my particular information. It appears that
tblSalesData should be my "Order Details Table" (table from which form is
created). I would enter my essett number and essett date.

I am lost on this portion --
"# WHERE [Payment Date] Between #" & ResultOfPreviousComputation &
"# And DateAdd("d",6,ResultOfPreviousComputation) & "#;"

Please excuse my ignorance. I greatly appreciate your assistance.

JR
I would hide the whole thing behind a command button.
[quoted text clipped - 45 lines]
 
J

jwr

Much clearer! Am I correct that tblSalesData should be replaced by
"tblOrderDetails" since that is the table used for my form?

You seem very knowledgeable regarding access. In the past I have submitted
questions regarding the ability to import from excel to access and update a
table. I will pose my question to you. If I need to post a new post, I
understand.

Once my information is analyzed in access by excel - and the information is
residing on a worksheet in excel, that worksheet is protected except for 2
cells and then emailed to another destination. The destination must input
information in these two cells and email back to my computer. Question:--
Is there a way once the emailed worksheet has been returned to my computer -
with the new information - to create a query to pull the new information
from the 2 fields in excel and insert into my original access query that was
analyzed by excel and then emailed? Or must I resort to manually inputting
the information on into my query?

Thanks in advance.

OfficeDev18 via AccessMonster.com said:
I will try to answer as well as play catchup on the information I didn't say
explicitly before.

Create a command button on your form, maybe call it cmdEnterEssett. In the
OnClick event of your new command button, do the following two related tasks:
1-calculate the start date to enter essett date, and 2-go ahead and modify
all appropriate records with the correct information.

Since essett day is Wednesday, I'm assuming the records to be updated are
dated last week Wednesday through Tuesday (yesterday). Therefore, your
OnClick event in VBA might look something like

Private sub cmdEnterEssett_OnClick()

Dim StartDate As Date

StartDate = DateAdd("d",-7,Date()) 'this gives last week Wednesday

DoCmd.RunSQL "UPDATE tblSalesData SET [Essett #]=[Enter Essett Number],
[Essett Date] = #" _
& Date() & "# WHERE [Payment Date] Between #" & StartDate & _
"# And DateAdd("d",6,StartDate) & "#;"

End Sub

Again, you will be asked by the query to enter an essett number each time you
run the query.

By the way, the underscores at the end of each line of SQL is the
continuation character, which tells Access that the statement carries over to
the next line.

Sam
Thank you for your reply. I think I am lost in your reply. I know that you
are wanting me to input my information. May I quiz you more?

The essett date is always Wednesday.

Since I am completely ignorant regarding update queries, please tell me
where I need to enter my particular information. It appears that
tblSalesData should be my "Order Details Table" (table from which form is
created). I would enter my essett number and essett date.

I am lost on this portion --
"# WHERE [Payment Date] Between #" & ResultOfPreviousComputation &
"# And DateAdd("d",6,ResultOfPreviousComputation) & "#;"

Please excuse my ignorance. I greatly appreciate your assistance.

JR
I would hide the whole thing behind a command button.
[quoted text clipped - 45 lines]
Thank you in advance.
 
O

OfficeDev18 via AccessMonster.com

Am I correct that tblSalesData should be replaced by
"tblOrderDetails" since that is the table used for my form?

Certainly. I only used tblSalesData as an example.

Regarding the Excel-to-Access data. It can be done automatically, depending
on the original query. To explain, if the original query is not a totals
query, then a simple update query might be written to insert the two cells
into your Access table. If the original query is a totals query, that may be
impossible. That is to say, if you try it you will get an error message "must
use updateable query". In that case it can't be done in one step, and may be
more trouble than it's worth. If you really want to automate that process,
please post your original query's SQL so I can see what you would need to do.
Also, I would need to know the details of the Excel spreadsheet contents.

Sam
Much clearer! Am I correct that tblSalesData should be replaced by
"tblOrderDetails" since that is the table used for my form?

You seem very knowledgeable regarding access. In the past I have submitted
questions regarding the ability to import from excel to access and update a
table. I will pose my question to you. If I need to post a new post, I
understand.

Once my information is analyzed in access by excel - and the information is
residing on a worksheet in excel, that worksheet is protected except for 2
cells and then emailed to another destination. The destination must input
information in these two cells and email back to my computer. Question:--
Is there a way once the emailed worksheet has been returned to my computer -
with the new information - to create a query to pull the new information
from the 2 fields in excel and insert into my original access query that was
analyzed by excel and then emailed? Or must I resort to manually inputting
the information on into my query?

Thanks in advance.
I will try to answer as well as play catchup on the information I didn't say
explicitly before.
[quoted text clipped - 53 lines]
 
J

jwr

SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #],
[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));


From this query, I analyze with Excel to create the Excel worksheet. I then
open a new template worksheet that automatically places the above
information in the correct cells. The fields, JD Invoice #, JDInv$,
SerialNum, and ShipDate are entered by my email recipient and emailed back
to me. I then must take the information in these 4 fields and insert into
my access database.

I have tried exporting the data to Excel to create a MSQuery, but I get
messages that excel expected parameters. I cannot get beyond this. The
access people have told me that this is the way I should be handling the
query to excel info; however, it won't work and what I am doing by using
tools-analyze with excel works.

Thank you,
JR


OfficeDev18 via AccessMonster.com said:
Am I correct that tblSalesData should be replaced by
"tblOrderDetails" since that is the table used for my form?

Certainly. I only used tblSalesData as an example.

Regarding the Excel-to-Access data. It can be done automatically, depending
on the original query. To explain, if the original query is not a totals
query, then a simple update query might be written to insert the two cells
into your Access table. If the original query is a totals query, that may be
impossible. That is to say, if you try it you will get an error message "must
use updateable query". In that case it can't be done in one step, and may be
more trouble than it's worth. If you really want to automate that process,
please post your original query's SQL so I can see what you would need to do.
Also, I would need to know the details of the Excel spreadsheet contents.

Sam
Much clearer! Am I correct that tblSalesData should be replaced by
"tblOrderDetails" since that is the table used for my form?

You seem very knowledgeable regarding access. In the past I have submitted
questions regarding the ability to import from excel to access and update a
table. I will pose my question to you. If I need to post a new post, I
understand.

Once my information is analyzed in access by excel - and the information is
residing on a worksheet in excel, that worksheet is protected except for 2
cells and then emailed to another destination. The destination must input
information in these two cells and email back to my computer. Question:--
Is there a way once the emailed worksheet has been returned to my computer -
with the new information - to create a query to pull the new information
from the 2 fields in excel and insert into my original access query that was
analyzed by excel and then emailed? Or must I resort to manually inputting
the information on into my query?

Thanks in advance.
I will try to answer as well as play catchup on the information I didn't say
explicitly before.
[quoted text clipped - 53 lines]
Thank you in advance.
 
O

OfficeDev18 via AccessMonster.com

Does your email recipient need all that huge amount of information in order
to know where to enter the four fields, or does he download that information
to his own machine?

What I'm driving at is that if the email recipient needs, for example, only 3
or 4 fields in order to know which record gets which information, why copy to
Excel all those fields? Just make a query (using the above query as the data
source) with the fields he needs and export that to Excel. Then when you get
it back completed, you can update your database with a simple update query.
You won't need to go through a hoop to do that, by the way, because you're
not using a totals query.

Sam
SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #],
[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

From this query, I analyze with Excel to create the Excel worksheet. I then
open a new template worksheet that automatically places the above
information in the correct cells. The fields, JD Invoice #, JDInv$,
SerialNum, and ShipDate are entered by my email recipient and emailed back
to me. I then must take the information in these 4 fields and insert into
my access database.

I have tried exporting the data to Excel to create a MSQuery, but I get
messages that excel expected parameters. I cannot get beyond this. The
access people have told me that this is the way I should be handling the
query to excel info; however, it won't work and what I am doing by using
tools-analyze with excel works.

Thank you,
JR
Am I correct that tblSalesData should be replaced by
"tblOrderDetails" since that is the table used for my form?
[quoted text clipped - 38 lines]
 
J

jwr

Unfortunately, the recipient needs all the information.

OfficeDev18 via AccessMonster.com said:
Does your email recipient need all that huge amount of information in order
to know where to enter the four fields, or does he download that information
to his own machine?

What I'm driving at is that if the email recipient needs, for example, only 3
or 4 fields in order to know which record gets which information, why copy to
Excel all those fields? Just make a query (using the above query as the data
source) with the fields he needs and export that to Excel. Then when you get
it back completed, you can update your database with a simple update query.
You won't need to go through a hoop to do that, by the way, because you're
not using a totals query.

Sam
SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.ProductName,
Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice,
[Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price],
IIf([Total Price]>=0,[Total Price],Null) AS [Amount Before Trade-In],
IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS
[Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName,
Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode,
Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #],
[JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice
Amount]*Products!HandlingPct AS [Handling $]
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.ControlNum)>=[forms]![ParamControl#]![Beginning
ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending
ControlNum]));

From this query, I analyze with Excel to create the Excel worksheet. I then
open a new template worksheet that automatically places the above
information in the correct cells. The fields, JD Invoice #, JDInv$,
SerialNum, and ShipDate are entered by my email recipient and emailed back
to me. I then must take the information in these 4 fields and insert into
my access database.

I have tried exporting the data to Excel to create a MSQuery, but I get
messages that excel expected parameters. I cannot get beyond this. The
access people have told me that this is the way I should be handling the
query to excel info; however, it won't work and what I am doing by using
tools-analyze with excel works.

Thank you,
JR
Am I correct that tblSalesData should be replaced by
"tblOrderDetails" since that is the table used for my form?
[quoted text clipped - 38 lines]
Thank you in advance.
 
O

OfficeDev18 via AccessMonster.com

OK, let's continue.

What you need now, when you receive the return email from your recipient, is
an update query. This may not be easy, because while you're not running a
totals query (see my previous posting) the query is still quite complex, and
an update query that may need to be put on top of that query might still give
you the 'must use an updateable query' error message.

Try the following: In the Access query design interface, bring in your data
table - the table that is the ultimate residence of the four fields populated
by your recipient. Next, bring in the linked Excel sheet you received back.
Make joins as necessary, so Access will know which records to update. In the
Query Type icon (in the pull-down menu on the top of the interface), change
the type to "Update" query, as you want to 'update' the fields with the new
information.

In the query-by-example grid - Microsoft's name for the lower portion of the
query design interface - provide the names of the four fields to update in
the table (there are many ways to do this; I assume you know your way around
the QBE grid). On the 'Update To' line, provide the names of the
corresponding fields in the Excel sheet, and provide any necessary criteria.

In the upper left corner, click on 'Datasheet view' to "try out" the query;
see if it gives you the correct amount of records that will be updated, the
correct fields, any error messages, etc. BEFORE you try to make it do the
work.

Even if this approach doesn't work directly, it is at least the start of what
could be made to work.

Hope This Helps, though,

Sam
Unfortunately, the recipient needs all the information. >
Does your email recipient need all that huge amount of information in order
to know where to enter the four fields, or does he download that information
[quoted text clipped - 60 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query 2
Query design help 2
multi table query doesnt work! 4
Help on a query based on the latest date 5
Query Most Recent Payment 1
Match Problem 1
query for the most recent date 3
Previous received date 2

Top