create table using something like loop query

[learning_graccess]

hi,
I want to create a table like below
1st April, morning
1st april, afternoon


1st april, evening
1st april, night
2nd april, morning
2nd april, afternoon
2nd april, evening
2nd april, night
3rd april, morning
and so on

Is there anyway I can create such table using a query in some codes without doing manually?

[alansidman]

Here is a link to building tables using SQL

http://www.w3schools.com/sql/sql_create_table.asp

Alan

[learning_graccess]

sorry I want to insert those values into the table. Using "insert into" we can add only one row at once. Moreover loop is not possible so I'm looking for some other way to do that

[orange]

Try http://www.w3schools.com/sql/sql_insert.asp

If this doesn't resolve the issue, please describe what you are trying to do in plain English. Once we understand WHAT you are trying to do, someone may offer options for HOW it can be done.

[dipique]

In VBA this would be relatively easy--does it need to be a self-contained query?

[learning_graccess]

In VBA this would be relatively easy--does it need to be a self-contained query?

Can you please tell me how to do it?

@Orange
I have a blank table with two fields Date, Description. I want to place the above values in the table.

[recyan]

Something to think about, till the others come by :

You should have an empty table "MyTable" with fields "TheDate" & "Description".

 Function populateMyTable()
    Dim StartDate As Date
    Dim EndDate As Date
    Dim dbs As Database
    Dim TheStateValues(0 To 3)  As String ' Array of states
    
    TheStateValues(0) = "Morning"
    TheStateValues(1) = "AfterNoon"
    TheStateValues(2) = "Evening"
    TheStateValues(3) = "Night"
    
    StartDate = #4/1/2012#
    EndDate = #4/2/2012#
    
    Set dbs = CurrentDb

    NextDate = StartDate

    For i = NextDate To EndDate
   
        For Each varValue In TheStateValues
            j = varValue
            
            strsqlInsert = "insert into MyTable (TheDate, Description) VALUES (#" & NextDate & "#, '" & j & "')"
            dbs.Execute (strsqlInsert)
            
        Next varValue
        
        NextDate = DateAdd("d", 1, NextDate)
    Next
    
    MsgBox ("Successful")
End Function

PS : I am on a negative scale as far as VBA is concerned, Just see if above gives you some ideas.

Thanks

[learning_graccess]

Thanks very much
But i don't know how to make use of this code in access.. I mean where to place this code and how to run it in access.

[RagJose]

Just to give a quick, basic answer your question: codes like the above are placed (typed in) in Modules (an Access entity siding with tables, queries, forms, macros, reports) that Access creates (and prompts for content) as you enable specific Events for objects like a button in a form. They are activated by the occurrence of the Event (like clicking the button when the form is active). For more detail, I suggest you look up an Access VBA tutorial - if you never did it, a step-by-step procedure would be impractical.

Another (maybe simpler) way of populating such a table might be writing a Macro. There should be tutorials on that, too.

Now, if that's a one-time job, you might consider building the table as an Excel spreadsheet, then importing it into Access.

[dipique]

Thanks very much
But i don't know how to make use of this code in access.. I mean where to place this code and how to run it in access.

Just to get you started, here's an idea of how this might work. Let's say you want this table to be created when you press a button. So you'd create a blank form in design view and drop a button on the form. Access will automatically ask you what you want to do with the button, but just click cancel.

Click the button, and the properties for that button should display on the right side of the screen. If they don't, right click the button and choose "Properties".

In the properties sidebar, there will be a number of tabs, one of which will be labeled "Event"--click that one. What you see will be a list of all the things that happen to a button--"events"--that your form can respond to. The most common one, "On Click", will be right at the top. Click to the right of where it says "On Click" and click the little button with an ellipsis on it. Choose "Code Builder" and a VBA window will pop up with a tiny bit of pre-written code--something like this:

Private Sub Command0_Click()
    
End Sub

You have now created a sub-routine that will run every time you click the button. Put code in between the two pre-written lines to make things happen. For example, you could make it look like this:

Private Sub Command0_Click()
    MsgBox "Hey there, world."
End Sub

That code will show a message box that says "Hey there, world." Or, you can insert code like what recyan provided. Just make sure not to copy in the "Function..." line or the "End Function" line at the end.

That of course is just one possibility. There are lots of events that can be triggered, like when the form loads, or when keys are pressed, or almost anything else. Like recyan said, it's impractical to give a full tutorial on how to use VBA. I'd recommend http://www.functionx.com/vbaccess/ as a great place to learn how to use VBA in access.

Good luck!

Dan

[RagJose]

There's a totally different approach to yield the set of data you need, which does not require code. A simple query based on two small tables can do the job.

Create tblDays with two fields, dSeq (Number) and Day (Text). It will look like

dSeq	Day
1	1st
2	2nd
...	...
30	30th

Then tblPeriods, also with two fields, pSeq (Number) and Period (Text), and just four rows.

pSeq	Period
1	Morning
2	Afternoon
3	Evening
4	Night

A query that will look just like what you've illustrated would be:

SELECT [Day] & " April, " & [Period] AS Whatever
FROM tblDays, tblPeriods
ORDER BY tblDays.dSeq, tblPeriods.pSeq;

Name the query and use it as if it were a table.

As is, this solution covers just the month of April. It can be enhanced to cover a whole year if a third table (with month sequence, names and no. of days per month) is added. I leave it up to you as a challenge, but please feel free to ask for further help.

[learning_graccess]

Thanks recyan, rajgose, dipique.
<rajgose>
As to your last post, it causes to appear in single column. But I want them in two separate columns.

[RagJose]

No problem, just change first line of SQL query to

SELECT [Day] & " April, " AS Whatever1, [Period] AS Whatever2

[learning_graccess]

wow! it works.

But I have a little problem with it. Even after I order by "[Day]", the [period] is not as I want like [morning, afternoon, evening, night]. It displays [Afternoon, Evening, Morning, Night]

I want first "morning", then "afternoon", "evening", "night" against each day.

[RagJose]

That's why you need the "dSeq" and "pSeq" fields to order by. Ordering just by "Day" and "Period" will yield the alphabetical order you're getting. Check again post #11.