Update Union Query

[Guest]

Hello,

I am working with Access 2003 and am in desperate need of help.

I am unsure of how to go about this, but I have recently created a database
that allows me to enter data into 3 different ways (by cluster, group and
store). I have select queries for each to break down the groupings to the
the individual records needed (by store). I have then used a union query to
have them all in one place.

There is one field that needs to be updated after all of the above is done
and am unsure how to go about it. The field is called Case Proration

Any help I can get is greatly appreciated.

Thanks in advance!

 

[Douglas J. Steele]

Union queries are not updatable. You're going to need to take a different
approach, either using multiple subforms rather than a single form with the
results of the Union, or else using VBA to run an Update query.

 

[Guest]

The forms will update a table, correct?

My problem is that I have the 3 main tables of which data is entered. To
prevent the user from entering incorrect data, I am using the select query to
show the remaining information based on what the user enters into the table.

I did have these select queries as make table queries and I figured then I
could use the forms. However, as time goes on and more data is entered, I
would have to run the make table queries again and that would wipe out
anything I entered on the forms.

I am not very familiar with update queries or VBA. Would I have to do a
make table query, then and update query to the new table and finally a union
query of all the updated tables?

Many thanks!

 

[Guest]

The forms will update a table, correct?

My problem is that I have the 3 main tables of which data is entered. To
prevent the user from entering incorrect data, I am using the select query to
show the remaining information based on what the user enters into the table.

I did have these select queries as make table queries and I figured then I
could use the forms. However, as time goes on and more data is entered, I
would have to run the make table queries again and that would wipe out
anything I entered on the forms.

I am not very familiar with update queries or VBA. Would I have to do a
make table query, then and update query to the new table and finally a union
query of all the updated tables?

Many thanks!

 

[Douglas J. Steele]

Hold on. Are you really talking about a UNION query (which is multiple
SELECT statements on similar tables joined together into one large resultant
recordset):

SELECT Field1, Field2
FROM Table1
UNION
SELECT Field1, Field2
FROM Table2

or are you simply talking about joining together multiple tables?

SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID

If the latter, as long as your query is updatable, the form will be
updatable. See http://office.microsoft.com/en-ca/access/HP051880011033.aspx
for tips on how to ensure that the query is updatable.

 

[Guest]

Below is the code and it works great on putting the 3 queries together:

SELECT [CORPORATE NUMBER], [STORE NAME], [GROUP], [CLUSTER], [REGION],
[DIVISION], [START DATE], [END DATE], [MONTH], [YEAR], [NUMBER OF DAYS IN
MONTH], [DISPLAY LOCATION], [DISPLAY STATUS], [DISPLAY TYPE], [NUMBER OF
ENDCAPS], [NUMBER OF SIDECAPS], [NUMBER OF WOOD BOXES], [REQUIRED ENDCAPS],
[REQUIRED SIDECAPS], [REQUIRED WOOD BOXES], [FLEX ENDCAPS], [FLEX SIDECAPS],
[FLEX WOOD BOXES], [UPC], [ITEM DESCRIPTION], [SIZE], [VARIETAL], [DIRECT
IMPORT], [RETAIL PRICE PER BOTTLE], [COST PER BOTTLE], [SUGGESTED DEAL
RETAIL], [DEAL COST PER BOTTLE], [CASE PRORATION], [REQUIRED SIGNAGE], [AD
SUPPORT], [DEMOS], [EXIT STRATEGY], [LINKS IN STORE POG], [COMMENTS], [BDM
STATUS], [RANKING], [DISPLAY LOCATION TYPE]
FROM [PLANNER STORE SELECT QUERY]
UNION SELECT [CORPORATE NUMBER], [STORE NAME], [GROUP], [CLUSTER], [REGION],
[DIVISION], [START DATE], [END DATE], [MONTH], [YEAR], [NUMBER OF DAYS IN
MONTH], [DISPLAY LOCATION], [DISPLAY STATUS], [DISPLAY TYPE], [NUMBER OF
ENDCAPS], [NUMBER OF SIDECAPS], [NUMBER OF WOOD BOXES], [REQUIRED ENDCAPS],
[REQUIRED SIDECAPS], [REQUIRED WOOD BOXES], [FLEX ENDCAPS], [FLEX SIDECAPS],
[FLEX WOOD BOXES], [UPC], [ITEM DESCRIPTION], [SIZE], [VARIETAL], [DIRECT
IMPORT], [RETAIL PRICE PER BOTTLE], [COST PER BOTTLE], [SUGGESTED DEAL
RETAIL], [DEAL COST PER BOTTLE], [CASE PRORATION], [REQUIRED SIGNAGE], [AD
SUPPORT], [DEMOS], [EXIT STRATEGY], [LINKS IN STORE POG], [COMMENTS], [BDM
STATUS], [RANKING], [DISPLAY LOCATION TYPE]
FROM [PLANNER GROUP SELECT QUERY]
UNION SELECT [CORPORATE NUMBER], [STORE NAME], [GROUP], [CLUSTER], [REGION],
[DIVISION], [START DATE], [END DATE], [MONTH], [YEAR], [NUMBER OF DAYS IN
MONTH], [DISPLAY LOCATION], [DISPLAY STATUS], [DISPLAY TYPE], [NUMBER OF
ENDCAPS], [NUMBER OF SIDECAPS], [NUMBER OF WOOD BOXES], [REQUIRED ENDCAPS],
[REQUIRED SIDECAPS], [REQUIRED WOOD BOXES], [FLEX ENDCAPS], [FLEX SIDECAPS],
[FLEX WOOD BOXES], [UPC], [ITEM DESCRIPTION], [SIZE], [VARIETAL], [DIRECT
IMPORT], [RETAIL PRICE PER BOTTLE], [COST PER BOTTLE], [SUGGESTED DEAL
RETAIL], [DEAL COST PER BOTTLE], [CASE PRORATION], [REQUIRED SIGNAGE], [AD
SUPPORT], [DEMOS], [EXIT STRATEGY], [LINKS IN STORE POG], [COMMENTS], [BDM
STATUS], [RANKING], [DISPLAY LOCATION TYPE]
FROM [PLANNER CLUSTER SELECT QUERY];

I have the 3 so the user can enter the data in 3 different ways and then use
the queries to pull in data I don't want them messing with and break the
group and clusters down to the store level. It's just that the case
proration has to be done by store. I put the 3 queries in a union query so I
can display the results in a report from all 3 with ease. If you have a
suggestion that is different, but gets me to where I need, that's great too.
Just as long as the results are the same and I can enter the prorations by
store after the data is all compiled.

Again, many thanks!

Mel