Update a table based on a totals query of another table 2

[puforee]

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.

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,

 

[dhookom]

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

 

[puforee]

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,

 

[dhookom]

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

 

[puforee]

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])

 

[puforee]

Change the DLookUp to

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

 

[dhookom]

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

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

 

[puforee]

It is text. How do I deal with this?

 

[dhookom]

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):

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

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

 

[puforee]

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.

 

[dhookom]

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

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

=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

 

[puforee]

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

 

[Andrzejek]

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

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

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

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

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

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

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

 

[Andrzejek]

Thanks Duane,

Actually, to go from this:[tt]

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

to this
[tt]
"Select * from SomeTable " & vbNewLine _
& " Where LName = 'Brown' " & vbNewLine _
& " And Age = 23 " & vbNewLine _
& " And DOB = #1/1/1999#"
[/tt]
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...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

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

 

[puforee]

Thanks to both of you.