Intermediary tables / Intersection tables in many to many relation

[Guest]

Hello,
I have a db (Access 2000) where I have 3 tables one is a item detail table,
one is a product table and the one intermediary table since details and
products have many to many relationship.

Now the problem is: I want to copy the Item details table and when dping so
i change the month column to correspond to what the user wants i.e if the
item details that I am copying from has month as October....I copy all those
item details and paste them to same table but when doing so i change the
month to november.

end result is i have the same number of records for November now with
different item ID.

The intermediary table is just has item ID and product ID in it. I want to
copy the related data in the intermediary table and and past it back to
intermediary table with the newly added Item ID.

Please help I am ont sure on how to do this.

I hope all the above makes sense.

Thanks
J

 

[Jeff Boyce]

Josna

Why? For what purpose? Why are you making what sounds like a duplicate
table? Why are you making what sounds like "blank" records for the next
month?

It may be that there are other ways to accomplish what you are trying to do
by making "duplicates". In a well-normalized relational database (e.g.,
well-designed Access database) it is very rarely necessary to duplicate
table structure. Ususally, having duplicate table structure indicates
less-than-optimal table design...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Well, sorry Jeff, if this sounded like I was duplicating the table itself. I
was actually duplicating the data since we produce reports and the
report(items) specs do not change on the monthly basis for the most part.
Having said that i have forms which does allow the changes as well.

Any suggestions....

Thanks for replying though
J

 

[Jeff Boyce]

Again, I'll ask why? Are you creating duplicate data in order to create
reports? If you can explain a bit more about your reports, the newsgroup
readers may be able to offer other approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Ok...Sorry again there Jeff. Let me try ...

Process: In our department we generate reports monthly based on the
subscriptons of the client. So in case a client requires that he get reports
monthly then the proces is like from the main project numbers, we generate
sub numbers so that the reports get linked to the project. Then subscriptions
are set up and data sets are created. This all gets done on Oracle / Java
platform.

Now where does my Access application fits. Here from taking the report
parameters /specs for the month. Depending upon the clients input the report
specs may change. Then manually feeding the parameters in our BI tool and
then generating the reports. To solve this I have created thsi application
that takes in the report details and since the reports do not change much in
the monthly cycle I am coping the report details from last month and
displaying on the form. I am also giving projetc management the flexibility
to add reports for next month by copying the reports from previous month and
carring it over to next month I have had sussess in soing so. But i have now
realised that I will have to copy data in the related tableas as well so that
the copy cascades to other table as well. But when doing so I want then
related to the correct(new report ID)

I have some part of the sql figured out what i donot know is on how to get
the new report Id from the details table. here is the SQL if that helps..

strSQL = "INSERT INTO Report_Details ( Report_Name, Client_Id,
[Project_Manager_Id], Analyst_Id, Report_Module, View_Type_Code, Datapoint,
Driver_Dates," & _
" Supplemental_Data, Confidence_Interval_PCT,
Weighted_Market_Average, Hide_Market_Average, Color_Id, Sig_Testing, PDF,
Out_Type_Code, Subscription_Id, [Year], Analyst_Notes, [Month] ) " & _
"SELECT Report_Details.Report_Name,
Report_Details.Client_Id, Report_Details.[Project_Manager_Id]," & _
" Report_Details.Analyst_Id, Report_Details.Report_Module,
Report_Details.View_Type_Code, Report_Details.Datapoint,
Report_Details.Driver_Dates," & _
" Report_Details.Supplemental_Data,
Report_Details.Confidence_Interval_PCT,
Report_Details.Weighted_Market_Average, Report_Details.Hide_Market_Average,"
& _
" Report_Details.Color_Id, Report_Details.Sig_Testing,
Report_Details.PDF, Report_Details.Out_Type_Code,
Report_Details.Subscription_Id, Report_Details.[Year]," & _
" Report_Details.Analyst_Notes, IIf ([Month]= " & "'" &
Me![cbo_CopyFrom] & "'" & "," & "'" & Me![cbo_CopyTo] & "'" & ", [Month]) AS
Cycle " & _
" FROM Report_Details WHERE
(((Report_Details.[Project_Manager_Id]) " & strPM & ") AND
((Report_Details.Month)= " & "'" & Me![cbo_CopyFrom] & "'" & "));"
strSQL2 = "INSERT INTO Report_Products (Reports_Id, Products_Id)" & _
" SELECT Report_Products.Reports_Id,
Report_Products.Products_Id" & _
" FROM Report_Details INNER JOIN Report_Products ON
Report_Details.Report_Id = Report_Products.Reports_Id" & _
" WHERE (((Report_Details.Month)= " & "'" & Me![cbo_CopyFrom] &
"'" & "))" & _
" ORDER BY Report_Products.Reports_Id"

Sorry again thanks for being patient. Hope all the above helps.

Thanks again
J

 

[Jeff Boyce]

Josna

Sorry I'm not picking it up faster...

I'm still having difficulty visualizing why a set of data and/or a form
and/or a report would need to be duplicated.

When I create a report in Access, I use a query to return the data I need to
use/show in the report. This doesn't involve any copying/duplicating. If I
want to run the same report next month, I modify the query, and re-run the
report.

I have also set up a report-order form that lets me specify the data
criteria before running the report (same report definition, new selection of
data via the query).

I may be mis-reading your description, but it sounds like you create a new
table to hold the data for a new report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ok...Sorry again there Jeff. Let me try ...

Process: In our department we generate reports monthly based on the
subscriptons of the client. So in case a client requires that he get
reports
monthly then the proces is like from the main project numbers, we generate
sub numbers so that the reports get linked to the project. Then
subscriptions
are set up and data sets are created. This all gets done on Oracle / Java
platform.

Now where does my Access application fits. Here from taking the report
parameters /specs for the month. Depending upon the clients input the
report
specs may change. Then manually feeding the parameters in our BI tool and
then generating the reports. To solve this I have created thsi application
that takes in the report details and since the reports do not change much
in
the monthly cycle I am coping the report details from last month and
displaying on the form. I am also giving projetc management the
flexibility
to add reports for next month by copying the reports from previous month
and
carring it over to next month I have had sussess in soing so. But i have
now
realised that I will have to copy data in the related tableas as well so
that
the copy cascades to other table as well. But when doing so I want then
related to the correct(new report ID)

I have some part of the sql figured out what i donot know is on how to get
the new report Id from the details table. here is the SQL if that helps..

strSQL = "INSERT INTO Report_Details ( Report_Name, Client_Id,
[Project_Manager_Id], Analyst_Id, Report_Module, View_Type_Code,
Datapoint,
Driver_Dates," & _
" Supplemental_Data, Confidence_Interval_PCT,
Weighted_Market_Average, Hide_Market_Average, Color_Id, Sig_Testing, PDF,
Out_Type_Code, Subscription_Id, [Year], Analyst_Notes, [Month] ) " & _
"SELECT Report_Details.Report_Name,
Report_Details.Client_Id, Report_Details.[Project_Manager_Id]," & _
" Report_Details.Analyst_Id, Report_Details.Report_Module,
Report_Details.View_Type_Code, Report_Details.Datapoint,
Report_Details.Driver_Dates," & _
" Report_Details.Supplemental_Data,
Report_Details.Confidence_Interval_PCT,
Report_Details.Weighted_Market_Average,
Report_Details.Hide_Market_Average,"
& _
" Report_Details.Color_Id, Report_Details.Sig_Testing,
Report_Details.PDF, Report_Details.Out_Type_Code,
Report_Details.Subscription_Id, Report_Details.[Year]," & _
" Report_Details.Analyst_Notes, IIf ([Month]= " & "'" &
Me![cbo_CopyFrom] & "'" & "," & "'" & Me![cbo_CopyTo] & "'" & ", [Month])
AS
Cycle " & _
" FROM Report_Details WHERE
(((Report_Details.[Project_Manager_Id]) " & strPM & ") AND
((Report_Details.Month)= " & "'" & Me![cbo_CopyFrom] & "'" & "));"
strSQL2 = "INSERT INTO Report_Products (Reports_Id, Products_Id)" & _
" SELECT Report_Products.Reports_Id,
Report_Products.Products_Id" & _
" FROM Report_Details INNER JOIN Report_Products ON
Report_Details.Report_Id = Report_Products.Reports_Id" & _
" WHERE (((Report_Details.Month)= " & "'" & Me![cbo_CopyFrom] &
"'" & "))" & _
" ORDER BY Report_Products.Reports_Id"

Sorry again thanks for being patient. Hope all the above helps.

Thanks again
J