[Solved] Query dates by month

[younghammer]

I'm fairly new to Base and ran into a wall.

I have a database "Customers" containing over 600 entries, with first names, last names, addresses (#, street, zip), birth dates and sites visited. I want to make a query that will list my customers by month of birth, so I can send them a birthday card. My DOB field is of DATE type and looks like MM/DD/YY.

I tried using the query wizard and I cannot figure out the criteria I should using. Nothing works and I'm stalled.

Please, help, and try to keep it simple for me. I know nothing of mySQL and Basic.

Thank you.

[DACM]

...I know nothing of mySQL and Basic.

Are you using a MySQL database? Or does Base say "Embedded database" in the status bar at the bottom of your main Base window? Or perhaps something else?

Perhaps see:
https://www.google.com/search?sugexp=ch ... uery+month

Basic (language), on the other hand, is not necessary to solve this issue.

[younghammer]

It says Embedded Database.

[Villeroy]

Select all columns (*) plus a calculated month column:

Code: Select all

SELECT *, MONTH("Birth Date") AS "Month" 
FROM "Your Table" 
ORDER BY "Month"

[Sliderule]

Just to add on to the sound advise given by Villeroy above, assuming you want to only see the records from your table ( "Customers" ), and, you want want to be prompted to input a given month number ( 1 to 12 ) to bring back all the records where "DOB" is that month, follow these steps:

1. Open your OpenOffice Base file ( *.odb )
2. On the left, click on the Queries icon
3. Under Tasks, click on: Create Query in SQL View...
4. Copy and paste the following SQL
   

   Code: Select all

   SELECT 
      TO_CHAR( "DOB", 'MM/DD' ) AS "Birthday Date", 
      "Customers".* 
   
   FROM "Customers" AS "Customers" 
   
   WHERE MONTH( "DOB" ) = :Enter_Month_Number_1_to_12 
   
   ORDER BY "Birthday Date" ASC, 
            "Customers"."DOB" ASC

5. Run your Query, EITHER:
   1. F5 key
   2. Run Query icon on Toolbar
   3. From the Menu: Edit -> Run Query
6. When you are prompted, enter the month number . . . a number . . . from 1 to 12
7. Save the Query so you can run it again / later

Explanation: The above Query will display month and day ( 'MM/DD' ) of the "DOB" field as "Birthday Date". And, you are prompted, so, only records for a given input month ( 1 to 12 ) will be returned. Additionally, the records returned will be in the ORDER ( sorted ) by DAY within the given month.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[younghammer]

Sliderule:

It works perfectly! I appreciate how you kept your explanations simple and easy to follow. Now I can go forward with my birthday card idea.

Thank you!

[gr8fulbishop]

I have seen a lot of really good information and solutions, but some of it is not working for me. I’m not sure what I am doing wrong. I have a list of data on properties in my area. I am able to create a pivot table with the data that I need. However, I am trying to group the data by month. The original dataset has the date formatted as “date” - mm/dd/yyyy.

However, when I click on the first date in the pivot table and click “Data” -> “Group and Outline” -> “Group", it creates a copy of the date field instead of displaying a popup.

Is there something that I have done wrong? Data file is attached. Thanks for you assistance.

[Sliderule]

gr8fulbishop:

Just as an FYI ( For Your Information ) . . . your question here is really a Calc ( Data Pilot ) question, rather than a Base question, so, I would suggest if you have additional questions about Data Pilot, ask in the Calc forum.

To answer your question, I have attached below, a copy of your Calc file, using Data Pilot, and, having it GROUP by month. Below, I described what I did.

1. I opened your Calc file.
2. In Sheet1, I changed ( altered ) the Close Date column, that is, I 'converted' the TEXT values to Date values ( I removed the beginning single quote ( ' ) so the result was a real date ( stored in the spreadsheet as an integer ).
3. In your Data Pilot, in sheet: Pivot Table_Sheet1_1 , click on one of the dates, for example, 08/27/12
4. Press F12, OR, From the Menu: Data -> Group and Outline -> Group...
5. In the Grouping popup, in the Group By section, click the radio button for Intervals, and, choose, Months
6. Click on the OK button
7. Smile and say: "Gee Sliderule, that was easy. Now all I have to do is let the forum know I am able to Group my Calc output by Month, using DataPilot."

I hope this helps, please be sure to let me / us know.

Sliderule

[Nick N,]

@qwerty

Had to copy/paste yr spreadsheet and give correct format to each field.

I joined for you subsequent test file with your data listed in the report to suit your needs. Pls look at it.

Regards

[robleyd]

Did you intend to post this in viewtopic.php?t=110671 ?