Search results

ok for some reason a dialog box for [Enter Reporting Week] opens twice. see attached word document for parameter box and SQL code.

never mind, when I set the parameters, I used text, it needed to be single. Works like a charm. for some reason I thought the field was reading as text.

I have a crosstab query. users need to run the query for any week of a year. It is always a YTD query. I have used this process on many select queries. Will a crosstab query not support this simple method? See attached screen shots.

I did determine the issue. The SQL table is way to large for MS Access. over 11 million records. I created a SQL view and attached the MS Access to that view and worked like a charm.

great question, I never opened the table in MS Access. In fact it shows DELETE for every record every field. I have never run into this before.

I have a query, and when I execute it, the correct data set is returned. However one field shows "DELETE" as the value for all records. when I ran it on the SQL server management studio as a test it ran fine. End user does not have access to the tables, so I created a small MS Access...

thank you, the "" worked just fine. I will google ZLS to understand why "" worked but not IS Null. Again many thanks.

I have a very simple table. It has 12 fields, but I am only interested in GroupId, and dimension. dimension has what I believe to be some null values. when I run a simple query, both fields as output, and field dimension sorted ascending, I get my correct results. But when I run it with IS...

correct, or you could also put a "parameter" in the query and let the user decide. For example create one table, then create a query against that table, and in the query criteria for the product line field put something similar to this Like [enter Product line Id i.e. 12345]&"*" then when an...

interesting, let me go down that path. And yes my data set is 100,000 of lines of orders. thank you.

I am sorry, I guess I did not post that result, I apologize, I tried this, and got no answer, had to end up killing the query SELECT Distinct Customeraccount,DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90 AND CustomerAccount='" & customeraccount &...

yep figured that out. again posted before I refreshed, new code above, but it still does not give correct value by customeraccount. MS Access answer set has one single value for all customers The excel JPG is the correct answer set. I would like to do this in MS Access, but cannot seem to...

thank you. code updated as follows SELECT DISTINCT dbo_Fact_IncomingOrders_Local.CustomerAccount, DSum("netsaleslocalcurrency","dbo_FACT_IncomingOrders_Local","IncomingOrdersDate > date()-90")/90 AS Rolling90Days, DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate...

sorry posted before I refreshed. Let me look at your code and give that a try. again many thanks

I did some more work, still only one number for each customer, but at least I am learning the date syntax. as you can see I had to hard code the 18 to start with 2/1/2015. How can I use a function to determine the 1st day of the current month. Do not want to change the code every day. SELECT...

ok just to learn and understand I simplified the query to just one calculation for this test. SELECT CustomerAccount, DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90 AND CustomerAccount=" & customeraccount) AS AvgDailyBooking FROM...

Ok I continued my research and tried this. SELECT CustomerAccount, DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90") AS AvgDailyBooking,DSUM("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate <NOW()-3") AS MNTHtoDate FROM...

excellent, I understand all of that. Yes I know that the IFERROR is not really needed in Access. So I have modified your statement as follows. SELECT CustomerAccount, Avg(netsaleslocalcurrency) AS AvgDailyBooking FROM dbo_Fact_IncomingOrders_local WHERE IncomingOrdersDate > Date()-90 GROUP BY...

I am terrible with date coding in MS Access. something I need to work on. In excel I can usually work it out. I have this excel sheet and I want to convert all the formulas to an equivalent MS Access query formula. Attached is the completed excel workbook. Any help creating this date driven...

simple table 6 columns startdate enddate custacct salescost salesprice salesqty the txt file is set up so that there is a header record, 4,999 lines of data, then the same header record, then 4,999 lines of data, then a header record etc etc. for as many lines as is in the download. This is a...