Query Criteria and Data Formats

I have a query against an ODBC VFP table linked to our sales order software. 
I have run the total sum to display a daily total of all sales orders 
entered, but need to have the data displayed not only by day but also by week 
and month. I cannot find the correct expression for the query or report to 
group by week and month as well as by day. Does anyone know how to do this?

PS I posted this about 2-1/2 hours ago and it seemed to be dropped from this 
thread, anyone know why?
0
Utf
11/2/2007 10:09:21 PM
access 16762 articles. 3 followers. Follow

4 Replies
718 Views

Similar Articles

[PageSpeed] 13

"Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
news:115E7F08-BC3C-42EA-BF6D-2013589C58D0@microsoft.com...

>I have a query against an ODBC VFP table linked to our sales order 
>software.
> I have run the total sum to display a daily total of all sales orders
> entered, but need to have the data displayed not only by day but also by 
> week
> and month. I cannot find the correct expression for the query or report to
> group by week and month as well as by day. Does anyone know how to do 
> this?

I would fire up the query builder and add a few new columns that have the 
month,  day , weekday etc that you need for your report.

Thus add all the columns you need from the table into the query grid.

Now, let's add a few more Columns that you will need.

you can type these expressions for each new column directly into the query 
builder as follows

myMonth: month[MyDateField])

and for week you can go

myWeek: cint(format([MyDateField],"WW"))

and, for day

day:  day([MyDateField]

I placed a command to convert the format "WW" command to a integer via the 
cint() function. I done this just in case you want to sort on your report.
(the format command is likely returning a text string and that will not sort 
correctly, hence, the cint() command)

So, simply build and save the above query. now simply use the above query 
for the data source of your report.

At this point you can use the group by options of the report wirter. In fact 
why not use the report wizard for the report. It has been a while but I 
believe the report writer and wizard will actually build the month 
expressions for you (regardless, if you add the above expressions to the 
query builder, then you wil have defined columns as month, day etc for use 
in the Report writer.


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com 


0
Albert
11/3/2007 2:42:38 AM
Thanks so much-I'm on the right track now!  Can you please advise what the 
formula would be to convert the week number into the date of the first day of 
that week?  For example:
 
This is week 45.  The first day was Sunday, first date of the week is 
Nov-4-2007.

Kristi

"Albert D. Kallal" wrote:

> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
> news:115E7F08-BC3C-42EA-BF6D-2013589C58D0@microsoft.com...
> 
> >I have a query against an ODBC VFP table linked to our sales order 
> >software.
> > I have run the total sum to display a daily total of all sales orders
> > entered, but need to have the data displayed not only by day but also by 
> > week
> > and month. I cannot find the correct expression for the query or report to
> > group by week and month as well as by day. Does anyone know how to do 
> > this?
> 
> I would fire up the query builder and add a few new columns that have the 
> month,  day , weekday etc that you need for your report.
> 
> Thus add all the columns you need from the table into the query grid.
> 
> Now, let's add a few more Columns that you will need.
> 
> you can type these expressions for each new column directly into the query 
> builder as follows
> 
> myMonth: month[MyDateField])
> 
> and for week you can go
> 
> myWeek: cint(format([MyDateField],"WW"))
> 
> and, for day
> 
> day:  day([MyDateField]
> 
> I placed a command to convert the format "WW" command to a integer via the 
> cint() function. I done this just in case you want to sort on your report.
> (the format command is likely returning a text string and that will not sort 
> correctly, hence, the cint() command)
> 
> So, simply build and save the above query. now simply use the above query 
> for the data source of your report.
> 
> At this point you can use the group by options of the report wirter. In fact 
> why not use the report wizard for the report. It has been a while but I 
> believe the report writer and wizard will actually build the month 
> expressions for you (regardless, if you add the above expressions to the 
> query builder, then you wil have defined columns as month, day etc for use 
> in the Report writer.
> 
> 
> -- 
> Albert D. Kallal    (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com 
> 
> 
> 
0
Utf
11/5/2007 6:35:00 PM
Thanks so much-I'm on the right track now!  Can you please advise what the 
formula would be to convert the week number into the date of the first day of 
that week?  For example:
 
This is week 45.  The first day was Sunday, first date of the week is 
Nov-4-2007.

Kristi


"Albert D. Kallal" wrote:

> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
> news:115E7F08-BC3C-42EA-BF6D-2013589C58D0@microsoft.com...
> 
> >I have a query against an ODBC VFP table linked to our sales order 
> >software.
> > I have run the total sum to display a daily total of all sales orders
> > entered, but need to have the data displayed not only by day but also by 
> > week
> > and month. I cannot find the correct expression for the query or report to
> > group by week and month as well as by day. Does anyone know how to do 
> > this?
> 
> I would fire up the query builder and add a few new columns that have the 
> month,  day , weekday etc that you need for your report.
> 
> Thus add all the columns you need from the table into the query grid.
> 
> Now, let's add a few more Columns that you will need.
> 
> you can type these expressions for each new column directly into the query 
> builder as follows
> 
> myMonth: month[MyDateField])
> 
> and for week you can go
> 
> myWeek: cint(format([MyDateField],"WW"))
> 
> and, for day
> 
> day:  day([MyDateField]
> 
> I placed a command to convert the format "WW" command to a integer via the 
> cint() function. I done this just in case you want to sort on your report.
> (the format command is likely returning a text string and that will not sort 
> correctly, hence, the cint() command)
> 
> So, simply build and save the above query. now simply use the above query 
> for the data source of your report.
> 
> At this point you can use the group by options of the report wirter. In fact 
> why not use the report wizard for the report. It has been a while but I 
> believe the report writer and wizard will actually build the month 
> expressions for you (regardless, if you add the above expressions to the 
> query builder, then you wil have defined columns as month, day etc for use 
> in the Report writer.
> 
> 
> -- 
> Albert D. Kallal    (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com 
> 
> 
> 
0
Utf
11/5/2007 6:37:01 PM
"Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
news:A9275C64-A7B2-4C93-8FC0-1B13C2EE8DAD@microsoft.com...
> Thanks so much-I'm on the right track now!  Can you please advise what the
> formula would be to convert the week number into the date of the first day 
> of
> that week?  For example:
>
> This is week 45.  The first day was Sunday, first date of the week is
> Nov-4-2007.
>

   [yourDate] - weekday([YourDate]) + 1

Simply use the day of week (weekday), and subtract what day of the week..and 
then + 1.....



-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com


0
Albert
11/5/2007 11:27:41 PM
Reply:

Similar Artilces:

Timer for Queries?
Does anyone know of a macro or add-on that I could use as a simple timer for queries? I don't like to use the CPU time in the Task Manager because of it's inaccuracy when not receiving full CPU usage. THanks for any suggestions -- cmungs Exactly what are you trying to accomplish? Are you trying to cause a query to run automatically every so often? If so, you will need to use the timer event on a form to do that. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "cmungs" <cmungs@discussions.microsoft.com> wrote in message news:88EC7019-045F-4EF...

Filtering Data
I have a spreadsheet that contains a column for Investigator Name, Pt. ID and Date of Enrollment as follows. I need to filter how many patients were enrolled by each site per month. Should I use an advanced filter, I'm not sure how to do this. Any help would be greatly appreciated. Investigator Pt. ID Date Dr. Black 01-001 08/09/03 01-002 08/09/03 01-003 08/12/03 01-004 08/13/03 Dr. Smith 03-001 08/16/02 03-002 08/16/02 03-003 09/06/02 03-004 09/06/02 03-005 09/06/02 03-006 09/20/02 Regards, Dee ...

How to dump smartlist definition data
We are migrating from GP 8 to GP 10 and we need to go through and cleanup the existing smartlists. I would like to be able to query the definition tables for smartlist and provide the user community a spreadsheet that contains each of the "favorites" created under each of the out-of-the-box smartlists so they can let me know which ones are not used anymore and can be cleaned up. The ASIEXP81 table seems to house the Favorites in it so I am almost there. However, I cannot find where to join to the main smart lists so these favorites can be grouped. Does anyone know how to ...

Erratic results from query criteria
I am getting different results from running the same query with the same selections. One moment it is all behaving as expected, the next it has gone haywire. (I have done what appears to me to be EXACTLY what I have done in another database, where it works perfectly every single time.) In a query I have, amongst others, the following fields: Category SubCategory Company I want to be able to select any OR ALL of the relevant fields. I have the following criteria: Like "*"&[Type Category otherwise leave blank for ALL]&"*" Like "*"&a...

Number and text format in the same call
I'm trying to change the format of the following" 12345678 to 123-45-678 -- in other words, adding the dashes. Some of the numbers are the same 8 digits, but also have a letter. They are currently: 12345678A to 123-45-678A Thank you, In a helper column put this formula and copy down.....then do Copy > PasteSpecial > Values on that helper column to eliminate the formulas and delete the original column if desired. =LEFT(A1,3)&"-"&MID(A1,4,2)&"-"&MID(A1,6,99) Vaya con Dios, Chuck, CABGx3 "Jim Davis" <Jim Davis@discussions.m...

Making data span 2 columns
Hi, I'm new to this community and new to the RMS system. I am trying to modify the PrintTransactionDetails sub in the receipt.xml to be 2 columns and put the entry.description information onto another row. The person who writes the descriptions likes to be wordy and I need the entire 40 columns in order to hold the text. I changed the 3 columns into 2 with a format of 71% for the item number or description and 29% for the sales amount. The description and a null field occupy the first row and the item number and amount occupies the second row. I have the transaction d...

how to assign color for a data type
Hi i have made a chart for sales of 12 items in 28 outlets over a period of 4 years (ie total sale for the year), not all outlets have all 12 items. the outlest only stock what they think they can sell. so they may have sold an item one year, and not sold it next year. when I make a bar chart for each outlet I have 4 bars, one for each year, and each item is stacked up on the other on the bar, now only problem is that when i select another store, the bars all change colors, as in even if item 7 were there in both shops, the color representing item 7 is diffrent. In other words, irrespective o...

Updating Data from two different worksheets
I have two worksheets, General Ledger and Out Ledger. The general ledge lists our inventory according to a LOT ID and the total number of item in that lot; i.e. column A: Lot ID, column B: Total Number of Pieces column C: Balance The Out Ledger lists the number of items sold from a particular lot i.e. column A: Lot ID, column B: Number Sold I want to be able to calculate the Balance in the General Ledger give the Number Sold in the Out Ledger. I am having difficulty in makin sure the Lot ID's match up and that the proper cells are referenced. Any suggestions/advice would be greatly appr...

Update Query ?
Is there a way to 1) Assign events when adding in data thru an update query for example if you append a bunch of data into a table could you then do some kind of an update query to say if there is an initial event and no others tag this one Renewal 1 etc.. the only real data you have to go on would be Customer A and the total amt .. the event, renewal event and the delta would all need to be added in example: Say Customer A comes in with an initial deal then Customer A renews their deal So, the data would look like when you're done.: Year Custo...

Matching data in tables
I have two tables of data, some of the rows are duplicated in these tables i want to find the data that is different. will an advanced filter do this? Chip Pearson has lots of info about working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Jo Davis wrote: > > I have two tables of data, some of the rows are duplicated in these tables i > want to find the data that is different. > > will an advanced filter do this? -- Dave Peterson Cheers Dave "Dave Peterson" wrote: > Chip Pearson has lots of info about working with duplicates at: >...

design query match anywhere?
In the design query I want to match the letters "ABC" anywhere in the column 'description'. I could not get instr to work by using: InStr(description, "ABC")) > 0 How else can I match ABC (upper and lowercase) to anywhere in my 'description' column? Thanks! Are you saying you put that InStr bit as a criteria under your Description field in the query designer? Try putting Like "*ABC*" as the criteria instead. Alternatively, you could add a computed field to the query InStr([Description, "ABC") and then put >0 as the ...

keep value if formula has no data
I have a worksheet that automatically updates everyday and works some calculations for me. How can I get the formula sheet to keep the value from the previous day if there is no data today. Because now it is filling in all my cells with zeros. Thanks -- Nicki Taylor Post your formula. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "NTaylor" <...

Unexpected Error- Running Advance Find Query
I am getting a message that says "Unexpected Error An Error has occurred" when I am running an advanced find query on two custom entities. It seems to be when I have certain custom fields included in my view that the query errors out. Any help on this would be greatly appreciated. Thanks so much. Just wanted to add that I seem to get errors when there is a 0.00 amount in these fields... "TJ" wrote: > I am getting a message that says "Unexpected Error An Error has occurred" > when I am running an advanced find query on two custom entities. It se...

Formula Dependant Conditional Formatting
I am wanting conditional formatting on an entire column of data (B). Each cell's format is dependent on the data in the cell directly to its left (A). When I select the Conditional Formatting for B1 and enter the formula to make it dependant on A1, it works fine. I then copy the formatting down the row. The only problem is that the format in all the cells are dependant on A1, not their respective cells from column A. Is there a way to copy the conditional format down a column that will cause the format's formula to change respectively like formulas in the cells themselves do? m...

CRM Data Migration 11-03-04
Hi, I would like to enquire how long will I need to take to migrate data from a table in an access database to MS CRM? For instance I wana migrate 10 columns in customer table in access database to MSCRM. How much effort need to be done? As I have not done any data migration before. So wat do I need to do n prep myself in order to start the data migration for my customer? any guidelines to assist me? Kindly assist me pls. thanks Johnson Dear Johnson, You should take a look at the data migration framework. This is on a cd which is delivered with crm, or you can download it from t...

pass through query
If a combobox is populated by a pass-through query, does it requery every time the form is closed and reopened? Thanks, Sam Yes. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Sam wrote: > If a combobox is populated by a pass-through query, does it requery every > time the form is closed and reopened? > Thanks, > Sam ...

How do I replicate data from primary worksheet to other worksheet
I want to copy data from my primary worksheet (which will be modified frequently) to other my other worksheets (which have filters applied to the data) I want these to be updated automatically. I have already set up the filters on the other worksheets. To put you in the picture, it is for a stock room system, with the current stock in the primary sheet and the stock per shelf in the other worksheets. Thankyou Your help is warmly welcomed! Hi Jon Why are you doing the filtering on a copy? Why not just filter the original? If you do want to copy all data to another sheet, then on Sheet2...

SQL2008 and Access 2007 Data Project
I am have SQL 2008 and Microsoft office 2007. I created a adp DB that connects to a DB in SQL 2008. Everything seems fine but I can’t edit the records in the Microsoft adp database. I have full rights to the db so that is not the issue and. In fact I go into SQL and edit the records without any issues. Another odd thing is I do have a command button set up that runs a delete stored procedure in SQL and updates a table in SQL and this works fine. Has anyone ran into this problem and if so is can you point me to the solution? Thanks Andy > Has anyone ran into this...

Formatting a cell for currency
What's the VB code for formatting a cell to show currency (with dolla sign and 2 decimals)? Worksheets("Sheet1").Cells(1,1).?????????=True What's the syntax? thank -- k48 ----------------------------------------------------------------------- k483's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079 View this thread: http://www.excelforum.com/showthread.php?threadid=27333 Hi one easy way: Set your format manually while recording a macro :-) -- Regards Frank Kabel Frankfurt, Germany "k483" <k483.1eulpz@excelforum-nospam.com&g...

a count field in a query?
Hello, I have a query in which I would like to create a field which increments by 1 for each record selected, so if there are 10 records selected by the query, this column would show numbers 1 to 10. Ideally I would like to have some text in front of each number, say invoice1, invoice2 etc. From a previous posting I realise that this is frowned upon but it would be the simplest solution for my problem! How can I do this? Thanks Geoff On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote: >Hello, > >I have a query in which I would like to create a field which >...

Concatenate and Format of result...
I have a simple formula that concatenate values from several cells: =CONCATENATE(ROUND(D223,0), " ",ROUND(D224,0), " ",ROUND(D225,0), " on ",D227). D223, D224 and D225 need 1000 comma separators in the result so that D223, for example, would read 195,844 and not 195844 in the concatenated string. As it is, these three cells do have the thousands separator format but the format doesn't transfer to the concatenated result. My thanks in advance for any suggestions here. Cheers! Brad =TEXT(ROUND(D223,0),"#,##0")&" "&TEXT(RO...

Multiple criteria
Ok I have a workbook I am working on for someone else and my formulas will not work. Any help would greatly be appreciated. =SUMPRODUCT(--(C4:C59>=5900),--(C4:C59<=5999)) this works like a charm but I need to use this plus (e4:e59="g-shift"). I need the first formula to be the condition to count the second formula. so basically i want if c4 has 5950 and E4 has g-shift then I get 1 and if e4 is blank then i get 0. Everything I have tried says that the formula is wrong. Did you try: =SUMPRODUCT(--(C4:C59>=5900),--(C4:C59<=5999),--(e4:e59="g-shift")) I...

Saving Username and Pass with Web Queries
I am pulling data from a website that requires you to provide a usernam and password everytime you visit the site. Excel has cachin capabilities, but everytime you close excel and try and open up th spreadsheet again, it can't login. What is the fix for this? Thanks, Hend -- Message posted from http://www.ExcelForum.com Bump! --- Message posted from http://www.ExcelForum.com/ ...

Conditioning formating a data value to change to a color
Hello, What I am trying to do is lets say I have a bunch of numbers in column F (for illustration purposes I will only show one number) and what I would like if any of the numbers in column F are greater than 300 I want it to change the color of that number to red automatical (See example) date time day evening night 24hr. Totals 3/12/04 8:00 100 12:00 300 4:00 600 1000 (change to red) I have no problem with changing the number to red m...

Birthday query for many children in one record
I have a database for the church, it has one main flat table only. Each record for the parents have field for child1 name, child1birthday, child2 name, child2birthday, child3 name, child3birthday - up to 5 children. I am having difficulty trying to get a report to list the birthdays of the children like this parent name, child 1 name, child 1 birthday month etc. so you get something like this:- Parent John Smith, child Mary Smith January 3 Parent Fred Jones, child Bert Jones March 4 Parent John Smith, child Jane Smith March 6 Parent Harry Brown, child Jane Brown March 6 Parent Bert Taylor,...