refreshing queries prior to UNION

k2tile

Registered User.
Local time
Today, 09:47
Joined
Sep 22, 2010
Messages
19
First of all, this board is great. So many people posting questions and answers. I've only been using Access for a week or two so I don't have many answers yet, but once I do I'll definitely be here helping others.

My question is, how do I force a query to refresh itself when it's being called by another query? Specifically, there's a UNION query that combines the results of several other queries. Some of the source queries spit out the current date/time that the query was run, and I'm noticing that if I run the UNION query at a later date those dates are not changing. This tells me that the output of the source queries is being cached.

Is there a setting that would force the source queries to recalculate? Ultimately I am looking for a 'one button' solution that would simultaneously refresh all data and output the final combined results to .csv. Should I be looking for a way to do this using VBA?

Thanks
 
A query always has the latest data. When you run a union query the queries that make up that union are pulling the latest stuff. You don't need to concern yourself with refreshing them.
 
Oh, I see that you say you have dates that aren't being changed when you run the query? I think we'd need to know what the SQL behind each applicable query is, in order to find out what is going on.
 
Here goes...

Code:
SELECT [ATTRIBUTES]![Attribute] & "-" & [FORMS]![Form] AS PartNumber, [FORMS]![Description] & " in " & [ATTRIBUTES]![Name] AS PartDescription, [FORMS]![Details] AS PartDetails, [FORMS]![UOM] AS UOM, " " AS UPC, "10" AS PartTypeID, IIf([FORMS]![PartsActive]=True,IIf([ATTRIBUTES]![PartsActive]=True,"True","False"),"False") AS Active, "true" AS Taxable, Mround([FORMS]![Cost]*[Price_Series]![CostMarkup],0.05) AS StdCost, [FORMS]![Weight] AS Weight, [FORMS]![WeightUOM] AS WeightUOM, [FORMS]![Width] AS Width, [FORMS]![Height] AS Height, [FORMS]![Length] AS Len, [FORMS]![SizeUOM] AS SizeUOM, [ATTRIBUTES]![Attribute] & "-" & [FORMS]![Form] & "-" & [Price_Series]![Series] AS ProductNumber, [FORMS]![Description] & " in " & [ATTRIBUTES]![Name] AS ProductDescription, [FORMS]![Details] AS ProductDetails, Mround([FORMS]![Price]*[Price_Series]![PriceMarkup],0.05) AS Price, IIf([FORMS]![ProductsActive]=True,IIf([ATTRIBUTES]![ProductsActive]=True,"True","False"),"False") AS ProductActive, "true" AS ProductTaxable, "10" AS ProductSOItemTypeID, [Vendors]![VendorName] AS Vendor, [Vendors]![VendorName] AS DefaultVendor, [ATTRIBUTES]![Attribute] & "-" & [FORMS]![Form] AS VendorPartNumber, FORMS!Cost*Price_Series![CostMarkup] AS Cost, [FORMS]![UOM] AS VendorUOM, False AS [Tracks-Lot Number], "" AS [Tracks-Revision Level], "" AS [Tracks-Expiration Date], "" AS [Tracks-Serial Number], "" AS AssetAccount, "" AS GOGSAccount, "" AS AdjustmentAccount, "" AS ScrapAccount, "" AS VarianceAccount, "" AS ABCCode, "" AS ProductSKU, IIf([FORMS]![SellOnline]=True,IIf([ATTRIBUTES]![SellOnline]=True,"True","False"),"False") AS [CFP-SellOnline], "" AS [CFP-Online Category 1], "" AS [CFP-OnlineCategory2], "" AS [CFP-OnlineCategory3], [ATTRIBUTES]![Attribute] & "-" AS [CFP-Product Variant], [ATTRIBUTES]![Name] AS [CFP-Variant Name], "Option" AS [CFP-Variant Type], Date() & " " & Time() AS [CF-UpdatedOn], Date() & " " & Time() AS [CFP-UpdatedOn]
FROM Vendors INNER JOIN (Price_Series INNER JOIN ((ATTRIBUTES INNER JOIN Collections ON ATTRIBUTES.Attribute = Collections.Attributes.Value) INNER JOIN FORMS ON Collections.Collection = FORMS.Collections.Value) ON Price_Series.Series = ATTRIBUTES.Series) ON Vendors.Vendor = FORMS.Vendor
WHERE (((FORMS.[Built?])=False));
The above query is called PPVP1. The other query is almost identical so I won't post it. They are both selecting only from static non-linked tables.

They are unioned by this bit of code:

Code:
SELECT * FROM ppvp1
UNION SELECT * FROM ppvp2;
 
Hi Bob,

Just checked out you site. Excellent stuff! I'm following a couple of your tutorials tonight and will be putting them to use tomorrow.

Looking forward to your reply in this thread. Also in my other thread about reusing the same field...I'm not sure why I didn't realize earlier, but your solution is definitely cleaner. No reason to run the query twice...
 

Users who are viewing this thread

Back
Top Bottom