INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Update a table based on a totals query of another table

Update a table based on a totals query of another table

(OP)
In my DB I have two Related Tables that I am working with. One table contains customer information:

Table 1
Customer (currently I have 3 customers)
License Purchased (currently from 32 to 72)
Date Purchased
Active licenses (This is what I need computed and stored in this table)

Table 2
This contains a list of licenses used by each customer, some other data fields and a field that shows the customer name. These licenses can be "Active" Y or N.
For this example we will say customer "A" has 32 licenses in table 1 and has 16 of these license in Table 2. Out of the 16 licenses in table 2 10 are active and 6 have expired and are not active. Etc. for customer "B" and "C".

Against Table two I run a totals query to count the "Active" = Y licenses for each customers. This returns each customer name (A, B, C for this example) and the total of active licenses. So customer "A" returns a value of 10.

My question. I need the results of the totals query to be added to the Active Licenses field in TABLE 1. I have tried an Update query to do this but I am failing to get results.

My Totals query is called Count_Customer_Actives. In my Update query, shown below, I felt it was necessary to connect the customer fields from each table so the correct records are updated. This may be my problem but I am not sure how to fix it. Please show me the correct query setup. SQL view will do nicely.

CODE -->

UPDATE Count_Cust_Actives INNER JOIN MS_Contracts ON Count_Cust_Actives.[Name or Location] = MS_Contracts.MS_Contract_Cust SET MS_Contracts.Active_Lic = [Count_Cust_Actives]!Count; 

I have done update queries before but for some reason this one is kicking my butt. My goal is to have this work even if my users add or remove customers.

Thanks,



RE: Update a table based on a totals query of another table

You can't create an updateable query that includes any totals/group by query.

Why are you storing information that can/should be calculated on-the-fly? This is typically considered bad design.

You could use DCount() in place of the totals query but again this enables questionable table design.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update a table based on a totals query of another table

(OP)
Duane,
I appreciate your question. And I assume you are talking about the Active Count.

I have a form that displays information from Table 1 and this includes the activity count.

But you just gave me an idea. I could base that form on a query. The query would have the table 1 (without Activity Count) fields connected to the totals query that does have the activity count. The connection would be the two Customer fields. This way I would NOT store the activity count in a table.

But, if I do this will I still be able to enter new customers on that form? Or would the query not allow updates to table 1?

Or--do you have thought on how to set up the for as a data entry for but still show the calculated Activity count (which would be zero for a new customer when it is first entered.

Thanks,

RE: Update a table based on a totals query of another table

Consider using your totals query as the record source for a subform that is linked to the appropriate fields. Maybe simpler would be using a text box with DCount().

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update a table based on a totals query of another table

(OP)
OK, working on Dlookup using example from the WEB. I am not putting this in the code feature so I can explain.

"Count" is from totals query "Count_Cust_Actives". "Name or Location" Is also field on from the totals query that is used to with the subform - [Forms]![Customer_Microsim_Itmes]![MS_Contract_Cust])field - as criteria. This expression returns #Name?. I know the syntax is messed up...but where.


=DLookUp("[Count]","Count_Cust_Actives","[Name or Location] =" & [Forms]![Customer_Microsim_Itmes]![MS_Contract_Cust])

RE: Update a table based on a totals query of another table

(OP)
Change the DLookUp to [code =DLookUp("[Count]","Count_Cust_Actives","[Name or Location] =" & [Forms]![Microsim Work Form]![Customer_Microsim_Items].[Form].[MS_Contract_Cust])code]

I forgot the main form. No the field with the DLookup continuously flashes Error

RE: Update a table based on a totals query of another table

Is [Name or Location] text or numeric? Your expression is built for numeric.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update a table based on a totals query of another table

(OP)
It is text. How do I deal with this?

RE: Update a table based on a totals query of another table

Where is the text box to display the count and where is the [Name or Location]?
I expect you could use something like this but I can't see your actual form(s):

CODE --> expression

=DLookUp("[Count]","Count_Cust_Actives","[Name or Location] =""" & [Forms]![Microsim Work Form]![Customer_Microsim_Items].[Form].[MS_Contract_Cust] & """") 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update a table based on a totals query of another table

(OP)
Duane,
You came through again. Your code string worked. Another star for you. Someday I may fully understand the nuances of how to use quotes in the code. I am without formal training on this so I really appreciate this forum and all the help you guys give.

In case you want to see the completed code I have added it. I added a check for Nulls and if Null returned a 0.

=IIf(IsNull(DLookUp("[Count]","Count_Cust_Actives","[Name or Location] =""" & [Forms]![Microsim Work Form]![Customer_Microsim_Items].[Form].[MS_Contract_Cust] & """")),0,DLookUp("[Count]","Count_Cust_Actives","[Name or Location] =""" & [Forms]![Microsim Work Form]![Customer_Microsim_Items].[Form].[MS_Contract_Cust] & """"))

Thanks again.

RE: Update a table based on a totals query of another table

Here is a link to some examples of using DLookup with different data types.

You should be able to shorten your expression using Nz()

CODE --> ControlSource

=Nz(DLookUp("[Count]","Count_Cust_Actives","[Name or Location] =""" & [Forms]![Microsim Work Form]![Customer_Microsim_Items].[Form].[MS_Contract_Cust] & """"),0) 

Calling DLookup() is somewhat expensive so only calling once is a good idea.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update a table based on a totals query of another table

(OP)
Thanks Duane,
I was not familiar with the NZ function.
I will use it.

RE: Update a table based on a totals query of another table

Quote (puforee )

Someday I may fully understand the nuances of how to use quotes in the code.

I usually start my SQLs with hard coded values and I make sure it works correctly, like:
Select * from SomeTable
Where LName = 'Brown'
And Age = 23
And DOB = #1/1/1999#


So it would look in my code like this (I have a Space at the beginning and end of my lines):
strSQL = "Select * from SomeTable " & vbNewLine _
& " Where LName = 'Brown' " & vbNewLine _
& " And Age = 23 " & vbNewLine _
& " And DOB = #1/1/1999#"


Then I replace all BLUE hard-coded values with some variables or controls:
strSQL = "Select * from SomeTable " & vbNewLine _
& " Where LName = '" & strLastName & "' " & vbNewLine _
& " And Age = " & intAge & vbNewLine _
& " And DOB = #" & Me.txtDOB & "#"


Your Criteria in your DLookUp function is just the WHERE part of your Select statement - the same rules apply with quotes.

BTW - I use vbNewLine at the end of my lines because the SQL looks nice when I do
Debug.Print strSQL smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Update a table based on a totals query of another table

Nice tip Andy. I couldn't have said it better and will keep this in mind when supporting other users.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update a table based on a totals query of another table

Thanks Duane,

Actually, to go from this:

Select * from SomeTable
Where LName = 'Brown'
And Age = 23
And DOB = #1/1/1999#


to this

"Select * from SomeTable " & vbNewLine _
& " Where LName = 'Brown' " & vbNewLine _
& " And Age = 23 " & vbNewLine _
& " And DOB = #1/1/1999#"

I have a little program (I wrote it myself) that first gets rid of all multiple Spaces, adds all " and & and _ and vbNewLine 'stuff' - pretty much formats it the way I want to - and then even dumps it into Clipboard so I can just paste it into my code. Works like a dream, especially for long SQLs.

I wish I could share it with others here on TT...pc2

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Update a table based on a totals query of another table

I have also created some code to write code but mostly stored procedure CRUD statements from table structures.

One on-line tool I use a lot is Instant SQL Formatter which takes a complex SQL statement and converts it to vb/vba. It saves me a ton of time.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Update a table based on a totals query of another table

(OP)
Thanks to both of you.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close