How to make a module out of a query

S

Sean

Below is the append query I have. The data comes from a spreadsheet where
the data changes everytime the preadsheet is generated. I have this append
query to gather the data from the spreadsheet and insert it into a table and
the query also adds the date it was added ( I use this table for historical
reporting). How do I convert this query into code i can put into a module
and how do I write it so it only performs this function once per day. So if
I put this code on a click event, it will check 1st to see if it has been ran
that day, if so, it will not run it and go onto the next event. Thanks,

INSERT INTO Backlog ( Import_Date, [Sales Doc#], Item, HgLvIt, [PO number],
[Create By], [Sold-to #], [Sold-to Name], [Ship-to Name], [Cust Val], [Fwd
Agent Name], Vendor, [Vendor Name], [Vendor PO], Material, [Plan Hier], SLoc,
Description, [Material Ref], [Customer Material Number], MRPCn, MRP, PrCtr,
[Product hierarchy], SU, [Del Grp], MD, SC, CH, HDB, LDB, PGRP, RPLT, [Spc
Stk In], [Order Ackn Nbr], Unit, [Created on], [Order qty], [Open Qty], [Open
Val], [Curr#], [Tgt GI Dt], [Rsch MA Dt], [On Hand Qt], [ATP Qty] )
SELECT Date(), [Sales Doc#], Item, HgLvIt, [PO number], [Create By],
[Sold-to #], [Sold-to Name], [Ship-to Name], [Cust Val], [Fwd Agent Name],
Vendor, [Vendor Name], [Vendor PO], Material, [Plan Hier], SLoc, Description,
[Material Ref], [Customer Material Number], MRPCn, MRP, PrCtr, [Product
hierarchy], SU, [Del Grp], MD, SC, CH, HDB, LDB, PGRP, RPLT, [Spc Stk In],
[Order Ackn Nbr], Unit, [Created on], [Order qty], [Open Qty], [Open Val],
[Curr#], [Tgt GI Dt], [Rsch MA Dt], [On Hand Qt], [ATP Qty]
FROM DailyBackLog;
 
P

Piet Linden

Below is the append query I have.  The data comes from a spreadsheet where
the data changes everytime the preadsheet is generated.  I have this append
query to gather the data from the spreadsheet and insert it into a table and
the query also adds the date it was added ( I use this table for historical
reporting).  How do I convert this query into code i can put into a module
and how do I write it so it only performs this function once per day.  So if
I put this code on a click event, it will check 1st to see if it has beenran
that day, if so, it will not run it and go onto the next event.  Thanks,
Click to expand...
See Allen Browne's website for some info on running queries in code...
http://www.allenbrowne.com/ser-60.html

I guess you could do something like this...

In a module, you can run an action query like this:
DBEngine(0)(0).Execute "Query1", dbFailOnError

The query runs without the dialogs, so SetWarnings is not needed. If
you do want to show the results, the next line is:
MsgBox DBEngine(0)(0).RecordsAffected & " record(s) affected."


If DBEngine(0)(0).RecordsAffected>0 then
SetCustomProperty("LastImportDate", Date())
End if

If GetCustomProperty("LastImportDate")<Date() 'import was not done
today
DBEngine(0)(0).Execute "Query1", dbFailOnError
End If
 
J

John Spencer

The absolute simplest code snippet I can think of would be

If DCount("*","Backlog","Import_Date = Date()") = 0 then
DoCmd.OpenQuery "NameOfQuery"
End IF

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
S

Sean

Thanks John, I'll give it a try. Could you give me some suggestions on the
following?

I have a query, call it my summary query, and it's built on top of ten other
query's that do not relate to each other, i.e. no joins. This allows me to
have 1 set of results in one query. Each of the 10 query's is summarizing
data which produce 1 field of data and that field is either a number or a
percentage, i.e. LateOrders 253, Orders shipped 450, Late Percent 33%,
KeyOrders 45, etc. Now, lets say there were no Key orders that day and the
Keyorder Query (stand alone query but is also 1 of the 10 query's for the
summary query) did not produce any data when you run it by itself. When the
summary query runs, even though the other 9 query's would produce results if
you ran them independently , they do not show any results in the summary
query because the KeyOrders query haad no results to display. So somehow if
1 quey has no results it prevents the others from showing results. Any
thoughts on how I could set this up differently? Could I set up some code to
do it and populate a table then query off the table? Can you give me an
example of the code as I am new to the coding aspect. Thanks so much and
below is my summary query for reference. The fields are differnet than my
explanation above.

SELECT NegativeATP.NegATP, ShipComplete.Ship_Complete,
TotalLines.TotalLines, ShippableLines.Shippable,
DeliveryGroup_Shippable.DeliveryGroup, TJ_UnShippable_Percentage.Percent AS
TJ_Percentage, GHO_UnShippable_Percentage.Percent AS GHO_Percentage,
BB_UnShippable_Percentage.Percent AS BB_Percentage,
BP_UnShippable_Percentage.Percent AS BP_Percentage
FROM NegativeATP, ShipComplete, TotalLines, ShippableLines,
DeliveryGroup_Shippable, TJ_UnShippable_Percentage,
GHO_UnShippable_Percentage, BB_UnShippable_Percentage,
BP_UnShippable_Percentage;




John Spencer said:
The absolute simplest code snippet I can think of would be

If DCount("*","Backlog","Import_Date = Date()") = 0 then
DoCmd.OpenQuery "NameOfQuery"
End IF

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Below is the append query I have. The data comes from a spreadsheet where
the data changes everytime the preadsheet is generated. I have this append
query to gather the data from the spreadsheet and insert it into a table and
the query also adds the date it was added ( I use this table for historical
reporting). How do I convert this query into code i can put into a module
and how do I write it so it only performs this function once per day. So if
I put this code on a click event, it will check 1st to see if it has been ran
that day, if so, it will not run it and go onto the next event. Thanks,

INSERT INTO Backlog ( Import_Date, [Sales Doc#], Item, HgLvIt, [PO number],
[Create By], [Sold-to #], [Sold-to Name], [Ship-to Name], [Cust Val], [Fwd
Agent Name], Vendor, [Vendor Name], [Vendor PO], Material, [Plan Hier], SLoc,
Description, [Material Ref], [Customer Material Number], MRPCn, MRP, PrCtr,
[Product hierarchy], SU, [Del Grp], MD, SC, CH, HDB, LDB, PGRP, RPLT, [Spc
Stk In], [Order Ackn Nbr], Unit, [Created on], [Order qty], [Open Qty], [Open
Val], [Curr#], [Tgt GI Dt], [Rsch MA Dt], [On Hand Qt], [ATP Qty] )
SELECT Date(), [Sales Doc#], Item, HgLvIt, [PO number], [Create By],
[Sold-to #], [Sold-to Name], [Ship-to Name], [Cust Val], [Fwd Agent Name],
Vendor, [Vendor Name], [Vendor PO], Material, [Plan Hier], SLoc, Description,
[Material Ref], [Customer Material Number], MRPCn, MRP, PrCtr, [Product
hierarchy], SU, [Del Grp], MD, SC, CH, HDB, LDB, PGRP, RPLT, [Spc Stk In],
[Order Ackn Nbr], Unit, [Created on], [Order qty], [Open Qty], [Open Val],
[Curr#], [Tgt GI Dt], [Rsch MA Dt], [On Hand Qt], [ATP Qty]
FROM DailyBackLog;
Click to expand...
Click to expand...
 
J

John Spencer

I might try a union query instead, but you still have the problem if no data
for one query, then you get a nothing returned for that one value

SELECT NegAtp as TheValue, "NegAtp" as ValueType
FROM NegativeATP
UNION ALL
SELECT Ship_Complete, "ShipComplete"
FROM ShipComplete
UNION ALL
SELECT TotalLines, "TotalLines"
FROM TotalLines
UNION ALL
....
UNION ALL
SELECT BP_UnShippable_Percentage.Percent, "BP_Percentage"
FROM BP_UnShippable_Percentage

You might be able to use DLookup instead to get all the values, DLookup should
return NULL if there are no rows returned by the query.

DLookup("NegAtp","NegativeATP")
DLookUp("Ship_Complete",ShipComplete")

You could actually use that with a one record table to string it all together
in one query or just return one record by limiting the results.

SELECT DLookup("NegAtp","NegativeATP") as NegAtp
, DLookUp("Ship_Complete",ShipComplete") as Ship_Complete
, ...
FROM SomeTable
WHERE PrimaryKey = 22

<<< since primary keys are unique you would get back only one record>>>

OR you could use Multiple subqueries instead of using the DLookup

SELECT (SELECT FIRST(NegATP) FROM NegativeATP) as NEGATP
, (SELECT FIRST(Ship_Complete) FROM ShipComplete) as Ship_Complete
, ...
FROM SomeTable
WHERE PrimaryKey = 22

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John, I'll give it a try. Could you give me some suggestions on the
following?

I have a query, call it my summary query, and it's built on top of ten other
query's that do not relate to each other, i.e. no joins. This allows me to
have 1 set of results in one query. Each of the 10 query's is summarizing
data which produce 1 field of data and that field is either a number or a
percentage, i.e. LateOrders 253, Orders shipped 450, Late Percent 33%,
KeyOrders 45, etc. Now, lets say there were no Key orders that day and the
Keyorder Query (stand alone query but is also 1 of the 10 query's for the
summary query) did not produce any data when you run it by itself. When the
summary query runs, even though the other 9 query's would produce results if
you ran them independently , they do not show any results in the summary
query because the KeyOrders query haad no results to display. So somehow if
1 quey has no results it prevents the others from showing results. Any
thoughts on how I could set this up differently? Could I set up some code to
do it and populate a table then query off the table? Can you give me an
example of the code as I am new to the coding aspect. Thanks so much and
below is my summary query for reference. The fields are differnet than my
explanation above.

SELECT NegativeATP.NegATP, ShipComplete.Ship_Complete,
TotalLines.TotalLines, ShippableLines.Shippable,
DeliveryGroup_Shippable.DeliveryGroup, TJ_UnShippable_Percentage.Percent AS
TJ_Percentage, GHO_UnShippable_Percentage.Percent AS GHO_Percentage,
BB_UnShippable_Percentage.Percent AS BB_Percentage,
BP_UnShippable_Percentage.Percent AS BP_Percentage
FROM NegativeATP, ShipComplete, TotalLines, ShippableLines,
DeliveryGroup_Shippable, TJ_UnShippable_Percentage,
GHO_UnShippable_Percentage, BB_UnShippable_Percentage,
BP_UnShippable_Percentage;




John Spencer said:
The absolute simplest code snippet I can think of would be

If DCount("*","Backlog","Import_Date = Date()") = 0 then
DoCmd.OpenQuery "NameOfQuery"
End IF

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Below is the append query I have. The data comes from a spreadsheet where
the data changes everytime the preadsheet is generated. I have this append
query to gather the data from the spreadsheet and insert it into a table and
the query also adds the date it was added ( I use this table for historical
reporting). How do I convert this query into code i can put into a module
and how do I write it so it only performs this function once per day. So if
I put this code on a click event, it will check 1st to see if it has been ran
that day, if so, it will not run it and go onto the next event. Thanks,

INSERT INTO Backlog ( Import_Date, [Sales Doc#], Item, HgLvIt, [PO number],
[Create By], [Sold-to #], [Sold-to Name], [Ship-to Name], [Cust Val], [Fwd
Agent Name], Vendor, [Vendor Name], [Vendor PO], Material, [Plan Hier], SLoc,
Description, [Material Ref], [Customer Material Number], MRPCn, MRP, PrCtr,
[Product hierarchy], SU, [Del Grp], MD, SC, CH, HDB, LDB, PGRP, RPLT, [Spc
Stk In], [Order Ackn Nbr], Unit, [Created on], [Order qty], [Open Qty], [Open
Val], [Curr#], [Tgt GI Dt], [Rsch MA Dt], [On Hand Qt], [ATP Qty] )
SELECT Date(), [Sales Doc#], Item, HgLvIt, [PO number], [Create By],
[Sold-to #], [Sold-to Name], [Ship-to Name], [Cust Val], [Fwd Agent Name],
Vendor, [Vendor Name], [Vendor PO], Material, [Plan Hier], SLoc, Description,
[Material Ref], [Customer Material Number], MRPCn, MRP, PrCtr, [Product
hierarchy], SU, [Del Grp], MD, SC, CH, HDB, LDB, PGRP, RPLT, [Spc Stk In],
[Order Ackn Nbr], Unit, [Created on], [Order qty], [Open Qty], [Open Val],
[Curr#], [Tgt GI Dt], [Rsch MA Dt], [On Hand Qt], [ATP Qty]
FROM DailyBackLog;
Click to expand...
Click to expand...
Click to expand...
 
Top