Get Data from a query and save it in a table by a button

[doobybug]

Hi,

I have 2 tables

Branches
BranchId, RegionId, City Name, Population, A, B, C, D, E, F, G

CityMarket

CityMarketId, BranchId, Market Name, A, B, C, D, E, F, G

I need to get the totals of A, B, C,... by Branch Id from City Market and input it in the table Branches.

All this is done by a button in a form

I have written this but is giving me a compile error:

DoCmd.RunSQL ("INSERT INTO Branches [A Outlets], [B Outlets], [C & D Outlets], [Wholesale General], [Wholesale Semi/Conf], [Catering], [Table Top], [Others]) & _ “SELECT CityMarket.BranchId, Sum(CityMarket.[A Outlets]) AS [SumOfA Outlets], Sum(CityMarket.[B Outlets]) AS [SumOfB Outlets], Sum(CityMarket.[C & D Outlets]) AS [SumOfC & D Outlets], Sum(CityMarket.[Wholesale General]) AS [SumOfWholesale General], Sum(CityMarket.[Wholesale Semi/Conf]) AS [SumOfWholesale Semi/Conf], Sum(CityMarket.Catering) AS SumOfCatering, Sum(CityMarket.[Table Top]) AS [SumOfTable Top], Sum(CityMarket.Others) AS SumOfOthers
FROM CityMarket
GROUP BY CityMarket.BranchId


Any ideas why?

 

[dhookom]

There are lots of issues with your code. Try this:

Dim strSQL as String
strSQL = "INSERT INTO Branches ([A Outlets], [B Outlets], " & _
  "[C & D Outlets], [Wholesale General], [Wholesale Semi/Conf], " & _
  "[Catering], [Table Top], [Others])"
strSQL = strSQL & "SELECT BranchId, Sum([A Outlets]), " & _
  "Sum([B Outlets]), Sum([C & D Outlets]), Sum([Wholesale General]), " & _
  "Sum([Wholesale Semi/Conf]), Sum(Catering), Sum([Table Top]), Sum(Others) " & _
  "FROM CityMarket GROUP BY BranchId"
DoCmd.RunSQL strSQL

BTW, I think your table structure is a bit un-normalized.

Duane
Hook'D on Access
MS Access MVP

 

[doobybug]

Hi,

I tried this but still no luck.

Basically if you can help me I want a button that when I click it would give me the totals of the sum of each outlet (Query must be by BranchId). The branchId can be obtained from the record that I would be clicked on on the main form (RegBranch1)

Any ideas why?

And why is it unnormalized?

I have

Branches

BranchId PK
City Name
Population
a outlets
b outlets ...

City Market

CityMarket PK
BranchId FK
CityMarket Name
a outlets
b outlets...

 

[dhookom]

still no luck" doesn't tell us anything about your actual error message or results.

I would place this line of code in your module

  "FROM CityMarket GROUP BY BranchId"
Debug.Print strSQL  'new line
DoCmd.RunSQL strSQL

You can then view the SQL by pressing Ctrl+G. If you want to use a specific BranchID, then you need to write this in your code. Assuming you have a numeric text box "txtBranchID" on your form that contains this code you could use the following. If BranchID is text, you will need to add more quotes.

   '...
strSQL = strSQL & "SELECT BranchId, Sum([A Outlets]), " & _
  "Sum([B Outlets]), Sum([C & D Outlets]), Sum([Wholesale General]), " & _
  "Sum([Wholesale Semi/Conf]), Sum(Catering), Sum([Table Top]), Sum(Others) " & _
  "FROM CityMarket " & _
  "WHERE BranchID = " & Me.txtBranchID & _
  "GROUP BY BranchID"
  '...

I think this is un-normalized because:
1) there are repeating similar fields for Outlets etc. These should be values stored in a field, not field names.
2) You are storing aggregated values that can be calculated.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

why is it unnormalized
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[doobybug]

This is what I have:

Private Sub GetData_Click()

Dim strSQL As String
strSQL = "INSERT INTO Branches ([A Outlets], [B Outlets], " & _
"[C & D Outlets], [Wholesale General], [Wholesale Semi/Conf], " & _
"[Catering], [Table Top], [Others])"
strSQL = strSQL & "SELECT Sum([A Outlets]), " & _
"Sum([B Outlets]), Sum([C & D Outlets]), Sum([Wholesale General]), " & _
"Sum([Wholesale Semi/Conf]), Sum(Catering), Sum([Table Top]), Sum(Others) " & _
"FROM CityMarket " & _
"WHERE BranchID = " & Me.BranchIdText & _
" GROUP BY BranchID"
Debug.Print strSQL
DoCmd.RunSQL strSQL

End Sub

However when the button is pressed nothing comes up! :S

Bdw about the unnormalization, I had to do that because I wanted the option to get the totals or that the users will input them himself as it is not always the case that they go to the second layer of CityMarket

 

[dhookom]

What do you see in the debug window? I see you missed at least 1 space in your code to the left of "SELECT".

I don't think you have provided enough information to justify the table structure.

Duane
Hook'D on Access
MS Access MVP

 

[doobybug]

This is the debug message

INSERT INTO Branches ([A Outlets], [B Outlets], [C & D Outlets], [Wholesale General], [Wholesale Semi/Conf], [Catering], [Table Top], [Others]) SELECT Sum([A Outlets]), Sum([B Outlets]), Sum([C & D Outlets]), Sum([Wholesale General]), Sum([Wholesale Semi/Conf]), Sum(Catering), Sum([Table Top]), Sum(Others) FROM CityMarket WHERE BranchID = 90 GROUP BY BranchID

And this is the code now:

Private Sub GetData_Click()

Dim strSQL As String
strSQL = " INSERT INTO Branches ([A Outlets], [B Outlets], " & _
" [C & D Outlets], [Wholesale General], [Wholesale Semi/Conf], " & _
" [Catering], [Table Top], [Others])"
strSQL = strSQL & " SELECT Sum([A Outlets]), " & _
" Sum([B Outlets]), Sum([C & D Outlets]), Sum([Wholesale General]), " & _
" Sum([Wholesale Semi/Conf]), Sum(Catering), Sum([Table Top]), Sum(Others) " & _
" FROM CityMarket " & _
" WHERE BranchID = " & Me.Text86 & _
" GROUP BY BranchID"
Debug.Print strSQL
DoCmd.RunSQL strSQL

End Sub

Still no success

So about the normalization:

I am trying to make something like this

In a Market for example Syria, You would have various cities ex: Damascus with different types of outlets. In each city there maybe different regions with different types of outlets.

Thus I have a table for the Market Containing an ID, name

A table for the Branches (examascus) with an ID and a fk refering to marketid + all outlets (numeric)

AND

a table called CityMarket containg an id and a fk refering to Branchid + all outlets(numeric)

I want the user to be able not to go to the third level of CityMarket to gain information as that data is not always available

 

[dhookom]

Your SQL needs to include the BranchID. I would also rename Text86 so you can understand your code. "Text86" makes no sense.

Private Sub GetData_Click()

Dim strSQL As String
strSQL = " INSERT INTO Branches (BranchID, [A Outlets], [B Outlets], " & _
  " [C & D Outlets], [Wholesale General], [Wholesale Semi/Conf], " & _
  " [Catering], [Table Top], [Others])"
strSQL = strSQL & " SELECT BranchID, Sum([A Outlets]), " & _
  " Sum([B Outlets]), Sum([C & D Outlets]), Sum([Wholesale General]), " & _
  " Sum([Wholesale Semi/Conf]), Sum(Catering), Sum([Table Top]), Sum(Others) " & _
  " FROM CityMarket " & _
  " WHERE BranchID = " & Me.Text86 & _
  " GROUP BY BranchID"
   Debug.Print strSQL
   DoCmd.RunSQL strSQL

End Sub

Regarding the normalization, what do you do when you need to add another outlet type? You shouldn't have to modify table structures to increase outlets.

Duane
Hook'D on Access
MS Access MVP

 

[doobybug]

Hi,

Sorry for the late response. This is now giving me a key violation!

 

[dhookom]

What have you done to troubleshoot the key violation error? If you haven't done anything start by identifying the unique keys and go from there.

Duane
Hook'D on Access
MS Access MVP

 

[doobybug]

The unique keys are for table Branches BranchId

and for

table MarketCity MarketCityId with fk BranchId!!

 

[doobybug]

Bdw in the table Branches the data is being appended but as a new record not with the record that has that BranchId

 

[dhookom]

So, what are you doing wrong? Your branches table is the one you are writing to.

You need to figure some of this out on your own. At least be able to identify why the problem is occurring. Then you can come back to Tek-Tips if you can't determine a solution.

Duane
Hook'D on Access
MS Access MVP

 

[doobybug]

Perhaps I need to use Update insert of insert as the record would have been already created?

 

[dhookom]

I would expect that is the issue. Of course it has been suggested that you shouldn't even save the aggregated records.

You won't be able to create an update query that uses a GROUP BY query.

Duane
Hook'D on Access
MS Access MVP

 

[doobybug]

This is what I did

I did a query named d-

SELECT Sum(CityMarket.[A Outlets]) AS SumOfA
FROM CityMarket
WHERE (((CityMarket.BranchId)=[Forms]![RegBranch]![RegBranch1].[Form].[BranchId]));


Then In sql I wrote this

Private Sub GetData_Click()

Dim SQL As String

SQL = "UPDATE Branches " & _
"SET Branches.[A Outlets] = d.[SumOfA]" & _
"Where (((Branches.[BranchId]) = " & Me.Text86 & "));"
DoCmd.RunSQL SQL

End Sub

However An enter parameter for d.[SumOfA] is popping up! Do u know why?

 

[dhookom]

Your query doesn't have "d" in the "FROM " or "UPDATE " clause. Even if it didn't this wouldn't work in Access.

IMO, you still haven't justified your table that stores aggregated values.

Duane
Hook'D on Access
MS Access MVP