Generating Table Records...

[lotharious]

I have 3 Tables
2 are Criteria Tables 1 is Detail Information.

I have a report that uses these tables and generates the rest of the information I need by using a query and report functions. However I need to generate a record for each of the matching criteria -> detail in a new table that will never change. This function would be ran on a weekly basis.

I See 3 ways to do this

First, Generate the data when generating the report.
This does not fit the requirements that I need for this information as these will end up being a validation process in the report where if the detail data, and the sum data do not match something has changed, which is a possibility.

Second, Make a form to generate the table records which can be manually or automatically ran.

Third Make a macro / query to do the same.

After a great deal of searching through these forums I have gotten a fwe ideas but not an answer on how to go about doing this. Since ive only been working with Access a week a lot of the things are still quite foriegn to me. I have yet to adventure into the VBA aspect yet, though I feel as if I should load up VC++ and find the API to do all that I need but that all will come later, maybe.

So, with that said, how do I go about doing this. I have not figured out how to generate the table records automatically. Nor do I know the best approach for this.

 

[lotharious]

Ok, so Ive come down to realizing I am going to need to do a VBA module for this. As it is more complex than I first realized. It may be possible to do what I need without but it would end up being very clunky.

 

[dhookom]

You should provide some sample records and what you expect as a result.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lotharious]

Ok, after all this time..
Before I really didnt know how to go about it, so I appologize for no detailed info.

I think I can get this working to generate my report once I figure out how to get data in VBA from the sql query.

I have this as my basis

Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
Set dbs = CurrentDb
strQueryName = "EndPeriodQuery"
dbs.QueryDefs.Delete strQueryName
Din Input1 as integer

Input1 = Inputbox("Enter Data")

strSQL = "Select field1, feild2, blah blah from table...." _
 & " WHERE field1 LIKE '" & Input1 "';"

Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

So now how do I get each returned row one at a time to put in specific variables thus being able to calculate and update/insert rows into the new table.

Then how do I open a report within this module so that it also uses the Input1 information. I can not call this VBA from the report itself as the report can be viewed by other people but they should not be able to update the tables with this if that makes sense.

I am learning.. One day I may be able to answer these simple questions for others.

 

[dhookom]

I don't see why you need to do anything with a querydef. Can't you just use a parameter query as the record source of your report?

I am totally confused by your statement

So now how do I get each returned row one at a time to put in specific variables thus being able to calculate and update/insert rows into the new table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lotharious]

I need to create records in another table as they will be used later, and changes at a later date can be made to other tables that would effect the results if the report was generated.

I need to do this for quite a few different things. This current one is going to be a pay generater for employees, which will later be added to it, the contractor and commission checks, as well as tracking information on a PO base by the hour per employee. Very hard to explain in short terms.

The basic layout of the code is this

----
select data1
while data1
select data2
generate newdata
select data3
generate newdata(part 2)
save newdata in table
end data1

Generate Report
----

data1 has 4 fields.
data 2 has 4 fields.
data 3 has 1 field.

Later this will expand into more complex loops and seperate functions. But once I get the basics I shouldnt have a problem with the rest.

As for qrydef Im just learning the vba aspect mostly from other posts and a few other help sites. So how do I get the fields into vba variables? I do not have a book atm that does much for vba though I am looking for one. Any good suggestions for that as well would be helpful. And Im not very good at asking understandable questions.

 

[dhookom]

I'm not sure if your question has to do with reports or generating records that will later be reported.

Your "generate newdata" might mean append records to a table or something else.

Can you clarify?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lotharious]

First I would like to appoligize about havig this long post here where it now really belongs in the VBA forum. At first I did not believe I was going to be heading this direction.

Sorry for the delay, was posting a reply earlier by lost power for some time.

The basis for this is a pay report which will grow to deal with many more things in the future. Currently this report is to be able to generate pay check information, and time usage ratio.

The generate data is actually the calculations which I have to run 2 select statements. The first will return a number of rows based on the pay period and employee. For each of those rows returned it must get the employee wage and caluclate gross wage, so it may do the tax info, and other stuff from another table which is irrelveant here from a couple more tables.

Since this is still a design in progress it is hard to be any more detailed. Currenlty Im dealing with a big cluster of tables that came in from Excel Spreadsheets not too long ago and making things work on a dynamic basis. Seperating tables, creating relations, and so forth.

What I have found is It seems I need to use a reordset in my VBA, however still lacking much knowledge about VBA (Reference book to arrive tomorrow I hope) I am having a hard time figuring much out, though I now actually have some code to put here not just semi pseudo code.

Most of this is copy and pasted from other forums and help/tutorials with only a little understanding

Dim dbjob As ADODB.Connection
Dim rsjob As New ADODB.Recordset
Dim arrTm As Variant
Set dbjob = CurrentProject.Connection

Dim PID As String
PID = InputBox("Input Pay Period")

strSQL = "SELECT [Time Clock].[PID], [Time Clock].[Emp ID], Sum([Time Clock].[Total Time]) AS " _
    & "[SumOfTotal Time] FROM [Time Clock] " _
    & "WHERE [Time Clock].PID LIKE '" & PID _
    & "' GROUP BY [Time Clock].[PID], [Time Clock].[Emp ID];"

rsjob.Open strSQL, dbjob

arrTm = rsjob.GetRows()

For i = 0 To UBound(arrTm, 2)
   Debug.Print i, arrTm(0, i) & "=" & arrTm(1, i)
Next i

 

[dhookom]

Lotharious,
Is there a question in the above or are you kinda working on it.

Please take your questions one at a time. Guys as old as me get confused when there are too many issues in a single thread.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lotharious]

Well, I just needed to know how to do recordset and get the information from the feilds of my query. I now have a book thankfully that explained how to get recordsets from ado and dao..
Thank you for your help dhookom 8)