Report repeating records

[tulikapuri]

I am developing a DB for a lab and at the end of the day i need to generate reports and send by email to supervisors, i had seven different reports to begin with, which i have brought down to 3, by creating relationships but the only problem is right now is that the tables for e.g. daily sand lab data, friability, Loi Molding Sand. Say, Daily sand lab data table has 7 records for one day and friability and Loi have only 1-2 records, now when i create a joined report, it repeats data again n again for the friability and LOI and instead of one page there are 3-6 pages in report, can someone please suggest me a simpler way of doing the same.

Thanks,
Tulika

[Rabbit]

To help we need to know what you're trying to report, what's in the tables, and how the tables are related.

[tulikapuri]

To help we need to know what you're trying to report, what's in the tables, and how the tables are related.

The tables have the values for date, time , unit, operator, mois, comp, GS, Perm etc for the Daily Sand Lab and it had the values date, time, unit, operator, wt. in pan, % friability for the friabilty of green sand and then date, time, unit, operator, wt. before heating, wt. after heating and % loi in Loi molding sand.

Now i want to related all the tables together to generate the report at the end of the day such that i dont need to put them in three different reports and email them, so i made relationships in a query to connect all the tables via primary key and then Date, so now i get all the values in the report but then since i have 7 values for daily sand lab and 2 each for fraibility of green sand and loi sand hence the report looks messed up completely and keeps repeating the loi and friability data.

Any help will be appreciated.

[Rabbit]

The tables have the values for date, time , unit, operator, mois, comp, GS, Perm etc for the Daily Sand Lab and it had the values date, time, unit, operator, wt. in pan, % friability for the friabilty of green sand and then date, time, unit, operator, wt. before heating, wt. after heating and % loi in Loi molding sand.

Now i want to related all the tables together to generate the report at the end of the day such that i dont need to put them in three different reports and email them, so i made relationships in a query to connect all the tables via primary key and then Date, so now i get all the values in the report but then since i have 7 values for daily sand lab and 2 each for fraibility of green sand and loi sand hence the report looks messed up completely and keeps repeating the loi and friability data.

Any help will be appreciated.

What's the primary keys?
Can you post the SQL of your query?
Can you provide some fictitious data to demonstrate the results you're looking for?

[tulikapuri]

I am trying to set up a report with sub reports but the same problem , can i talk to you or send you the zipped version of my DB .

[Rabbit]

I am trying to set up a report with sub reports but the same problem , can i talk to you or send you the zipped version of my DB .

I'm on a work computer and I can't download. But if you answer the questions from my previous post I'm pretty sure we can figure it out.

[tulikapuri]

I'm on a work computer and I can't download. But if you answer the questions from my previous post I'm pretty sure we can figure it out.

The primary keys are the numbers, i got the data from the three tables in the reports and now i am trying to set up a subreport using the other reports. I mean in the main report Daily Sand Lab i am trying to include the subreports for friability and LOI but i cant sort the data for each day, i had individual queries for all the reports which sorted the data on daily basis i.e. Criterion was Between [StartDate] And [EndDate].

Also i am not sure if i should enter the data in the page footer or report footer since it populates the data on every alternativve page leaving the rest of the pages blank.

Thanks Much for your help in advance. :)

[Rabbit]

The primary keys are the numbers, i got the data from the three tables in the reports and now i am trying to set up a subreport using the other reports. I mean in the main report Daily Sand Lab i am trying to include the subreports for friability and LOI but i cant sort the data for each day, i had individual queries for all the reports which sorted the data on daily basis i.e. Criterion was Between [StartDate] And [EndDate].

Also i am not sure if i should enter the data in the page footer or report footer since it populates the data on every alternativve page leaving the rest of the pages blank.

Thanks Much for your help in advance. :)

You said the primary keys are the numbers but you never told me which of your fields are numbers.

Can you post the SQL of your query?
Can you provide some fictitious data to demonstrate the results you're looking for?

[tulikapuri]

You said the primary keys are the numbers but you never told me which of your fields are numbers.

Can you post the SQL of your query?
Can you provide some fictitious data to demonstrate the results you're looking for?

I am making sand lab DB and i have the following SQL for Lab Sand Data:

SELECT [Lab Sand data].Operator, [Lab Sand data].Date, [Lab Sand data].TIme, [Lab Sand data].Unit, [Lab Sand data].Temp, [Lab Sand data].Mois, [Lab Sand data].MB, [Lab Sand data].[MB%], [Lab Sand data].Compactibility, [Lab Sand data].GS, [Lab Sand data].Perm, [Lab Sand data].[Sp Wt]
FROM [Lab Sand data]
WHERE ((([Lab Sand data].Date) Between [StartDate] And [EndDate]))
ORDER BY [Lab Sand data].Date, [Lab Sand data].Unit;

The following SQL for Friability of Green Sand :

SELECT [Friability of Green Sand].Date, [Friability of Green Sand].Operator, [Friability of Green Sand].Time, [Friability of Green Sand].Unit, [Friability of Green Sand].[% Moisture], [Friability of Green Sand].[MB Clay, ml], [Friability of Green Sand].[Comp %], [Friability of Green Sand].[Total Specimen Weight], [Friability of Green Sand].[Wt of Sand Collected in the Pan], [Friability of Green Sand].[% Friability]
FROM [Friability of Green Sand]
WHERE ((([Friability of Green Sand].Date) Between [StartDate] And [EndDate]));

The following SQl for LOI Molding Sand:

SELECT Loimold.Date, Loimold.Time, Loimold.Unit, Loimold.Operator, Loimold.[Sample Weight], Loimold.[Wt of crucible], Loimold.[Wt of crucible + Sample 9 Before heating)], Loimold.[Wt of crucible + Sample (After heating)], Loimold.[% LOI]
FROM Loimold
WHERE (((Loimold.Date) Between [StartDate] And [EndDate]));

Now i want to use Lab Sand Data as the main report and add friability of green sand and Loi molding as subreports, but i am not aware how to do it correctly and also since i have the criterion for all the individual report set to filter the data between StartDate and EndDate i am not sure how will we do this for one report.

In case you need some more information just let me know.

Thanks.

[Rabbit]

So basically you want one report to report records within a time period for Sand Lab, Friability, and LOI?

Make seperate reports for Friability and LOI and then include them as subreports in the Sand Lab report.

[tulikapuri]

So basically you want one report to report records within a time period for Sand Lab, Friability, and LOI?

Make seperate reports for Friability and LOI and then include them as subreports in the Sand Lab report.

I have done the same, but i am not able to add the subreports, can you please guide me how should i add a subreport, it populates date from everywhere and not within the give StartDate and EndDate.

[Rabbit]

I have done the same, but i am not able to add the subreports, can you please guide me how should i add a subreport, it populates date from everywhere and not within the give StartDate and EndDate.

Do you have a form where you enter the start and end date?

[tulikapuri]

No it prompts me when i open the report , i have set the criterion in the underlying query, but now when i have to add the subreports it asks me multiple times for the start date and the end date as i have set it for all the reports i.e. daily sand lab, friability, loi molding.

[Rabbit]

No it prompts me when i open the report , i have set the criterion in the underlying query, but now when i have to add the subreports it asks me multiple times for the start date and the end date as i have set it for all the reports i.e. daily sand lab, friability, loi molding.

You may want to think about doing it from a form and have the queries use the dates from the form instead. That way you'll only need to enter the dates once.

[tulikapuri]

Yess, but since i am new to Access i am not able to make the Subreport, based on all the information, when i tried doing that instead of populating data between the parameters it populated the report with all the data for friability and loi molding sand, how can i take care of that?

[tulikapuri]

How do i do it via form?

[Rabbit]

You would have two text boxes on your form, one for start date and one for end date. Let's call them txtStartDate and txtEndDate. And let's say they're on the form called frmReports

In your queries, instead of [StartDate] and [EndDate] you would use
[Forms]![frmReports]![txtStartDate]
[Forms]![frmReports]![txtEndDate]

[tulikapuri]

1. Ok , and how do i connect this to my report , i mean the report which has the sub-reports as well.

2. Also, please tell me where do i place the sub reports, in the detail box of the main report, the page footer or i need to do something other than this.

3. Can i place both the sub-reports one after the other in the same place or do i need to sort or group before doing the same.

Thanks,

[Rabbit]

1. Ok , and how do i connect this to my report , i mean the report which has the sub-reports as well.

2. Also, please tell me where do i place the sub reports, in the detail box of the main report, the page footer or i need to do something other than this.

3. Can i place both the sub-reports one after the other in the same place or do i need to sort or group before doing the same.

Thanks,

1) You wouldn't, your reports would stay the same. The form provides the dates for the queries and the reports get their data from the queries. So Form -> Query -> Report.

2) You would place the subreports in one of the headers of the main report.

3) You would place them however you want them displayed.

[tulikapuri]

I read in some of the related threads about can grow property, do i have to change the can grow property too?

Which header would i place the subreport? ya mean page header?

[Rabbit]

I read in some of the related threads about can grow property, do i have to change the can grow property too?

Which header would i place the subreport? ya mean page header?

You'll have to change the can grow property unless you know how big the subreports will be and that they won't change.

Well, which header you put it in depends on how you need it displayed. Page headers/footers repeat every page. Report headers/footers show only once. Group headers/footers repeat for every group.

[tulikapuri]

I got the report the way i needed it, by following the suggestions, but the only problem is i still have to enter the [Forms]![frmReports]![txtStartDate] ,[Forms]![frmReports]![txtEndDate] three times.

I replaced the Between [StartDate] And [EndDate] by Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate] in the individual queries for Daily Sand Lab, Friability of green sand and Loi.

[tulikapuri]

I was wondering that can i set the validation rules for all the values in the report such that for e.g. if the moisture is in the range 2.1-3.1 it should be green, if its in the range 2.1-1.5 , 3.1-3.6 it should be yellow and if its above 3.6 and below 1.5 it should be red.

Can i set the validation rules for all the fields, the above e.g. was only for one value, i have 6 more values like moisture, GS, Comp, Perm, MB, MB% with different validation rules.

Is it possible at all?

[Rabbit]

I got the report the way i needed it, by following the suggestions, but the only problem is i still have to enter the [Forms]![frmReports]![txtStartDate] ,[Forms]![frmReports]![txtEndDate] three times.

I replaced the Between [StartDate] And [EndDate] by Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate] in the individual queries for Daily Sand Lab, Friability of green sand and Loi.

Have you set up the forms? Because if you haven't set up the forms the way I specified earlier than they can't pull the data from anything if it's not available.

Having [Forms]![frmReports]![txtStartDate] does nothing unless you have a form named frmReports and a control named txtStartDate. The form also needs to be open with the dates filled in.

[Rabbit]

I was wondering that can i set the validation rules for all the values in the report such that for e.g. if the moisture is in the range 2.1-3.1 it should be green, if its in the range 2.1-1.5 , 3.1-3.6 it should be yellow and if its above 3.6 and below 1.5 it should be red.

Can i set the validation rules for all the fields, the above e.g. was only for one value, i have 6 more values like moisture, GS, Comp, Perm, MB, MB% with different validation rules.

Is it possible at all?

Yes, but it's not a validation rule. It's conditional formatting.

[tulikapuri]

yess, i have set up the form in the similar way you had desribed i have the form named frmreports and have two textboxes named txtstartdate and txtenddate and then have this set in the criteria for the individual queries for Daily sand lab, friability and loimold.

Now when i open the report , it asks me three time, do i need to have one query and do i need to set up some relationships ?

[Rabbit]

yess, i have set up the form in the similar way you had desribed i have the form named frmreports and have two textboxes named txtstartdate and txtenddate and then have this set in the criteria for the individual queries for Daily sand lab, friability and loimold.

Now when i open the report , it asks me three time, do i need to have one query and do i need to set up some relationships ?

Is the form open and are the fields filled in when you open the report?

[tulikapuri]

NO the form doesnt open when i open the report :(

[tulikapuri]

I have set up a macro frmreports , it will open the form frmreports and then open the report, but its still not working.

I also tried entering a report open in print preview command button in the form but its still not opening, i am sure its a link missing somewhere and i need your help.

[Rabbit]

You have to have the form open before you run the query. The dates also need to be filled into the controls before you run the query.

[tulikapuri]

You have to have the form open before you run the query. The dates also need to be filled into the controls before you run the query.

I have no idea how can i do this? can you plz describe me in detail? How do i open the form ?

I was trying to do it using the macro named frmreports which will openform frmreports and have set the filter for the form as Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate] and then in the next line i set it up to open the report but i dont see any data now.

I know what you are talking about, but i dont know how to get there?

[Rabbit]

I have no idea how can i do this? can you plz describe me in detail? How do i open the form ?

I was trying to do it using the macro named frmreports which will openform frmreports and have set the filter for the form as Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate] and then in the next line i set it up to open the report but i dont see any data now.

I know what you are talking about, but i dont know how to get there?

You open the form by double clicking the form.

Then you fill the dates by clicking in the control and typing in a start date. Then you click in the end date control and type in an end date.

Then you double click the report to open that.

[tulikapuri]

I did all this..........

I have a frm named frmreports and have two text boxes txtstartdate and txtenddate, now i have a command set in the form to open the report but it is empty even if i enter the dates, wot should i do?

I tried setting up a macro such that it opens the frmreport and then opens the frmreport.

Thanks Much

[Rabbit]

I did all this..........

I have a frm named frmreports and have two text boxes txtstartdate and txtenddate, now i have a command set in the form to open the report but it is empty even if i enter the dates, wot should i do?

I tried setting up a macro such that it opens the frmreport and then opens the frmreport.

Thanks Much

Try putting # hash signs # around the dates in the criteria. Between #date# and #date# where date is the field on the form.

[tulikapuri]

No nothing is working , everything else is perfect Rabbit, plz. tell me how can i get the parameters entered only once, and not three times, i cant do it?

[Rabbit]

What's the criteria for the queries?

[tulikapuri]

Hi ,

the criteria for the query is Between [Forms]![frmReports]![txtStartDate] And
[Forms]![frmReports]![txtEndDate].

I also have the frmreports in the form and i am trying to enter the above query in the individual queries for daily sand lab, friabilty and loimolding but when i try to open the report based on these queries it promts me for the txtstartdate and txtenddate three times, it will be good and time saving if i can enter it only once, i am sure there is a way and i am revolving around it but i am really sorry that i am not able to get to it.

Your help will be highly appreciated.

Thanks,

[Denburt]

If we can back up a few steps I think something major was missed.

You said the primary keys are the numbers but you never told me which of your fields are numbers.

Can you post the SQL of your query?

You posted the SQL to your queries and I have no idea how they may be related to each other. You must define a primary key in the main Table for the Main SQL then Foriegn Keys in the sub reports SQL query tables. If these don't exist then you can't link the sub reports to the master and this would cause such a problem... Posssible or did I miss somthing?

[Rabbit]

Hi ,

the criteria for the query is Between [Forms]![frmReports]![txtStartDate] And
[Forms]![frmReports]![txtEndDate].

I also have the frmreports in the form and i am trying to enter the above query in the individual queries for daily sand lab, friabilty and loimolding but when i try to open the report based on these queries it promts me for the txtstartdate and txtenddate three times, it will be good and time saving if i can enter it only once, i am sure there is a way and i am revolving around it but i am really sorry that i am not able to get to it.

Your help will be highly appreciated.

Thanks,

Did you try Between #[Forms]![frmReports]![txtStartDate]# And
#[Forms]![frmReports]![txtEndDate]#

[tulikapuri]

Did you try Between #[Forms]![frmReports]![txtStartDate]# And
#[Forms]![frmReports]![txtEndDate]#

There are two ways of adding subreport either do it manually from controls and then subreport or else have the query set up such that it takes the fields from all the tables and generate one report.

If i use this criterion Between [Forms]![frmReports]![txtStartDate] And
[Forms]![frmReports]![txtEndDate], for the query generated report it works, but if i manually add the subreports by dragging them in the report header, i dont see anything, and it asks me 12 times to enter the startdate and enddate.

I dont want to use the query to generate the report since the report doesnt come out to be in the format required, i can only get it if i do it via dragging the subreports.

Do u think this cud have an effect on wott we have been discussing ?

[Rabbit]

I'm getting confused, can you paste your current SQL?

[tulikapuri]

SQL of query?

[tulikapuri]

I have a query where i join all the tables i want in the report, i add the criterion in the date as Between #[Forms]![frmReports]![txtStartDate]# And
#[Forms]![frmReports]![txtEndDate], but when i generate the report , i dont use this query, i use individual reports which are based on different queries, becuase if i do it using the query the report doesnt come out in the format desired.

So i am making a report with subreports by draging the report in the report header. Now since this report is not linked to the query where i add the criterion hence i think its not working, but when i generate a report using the query it works.

[Rabbit]

Let's try to clarify. The setup is like this?

  Query1     Query2     Query3
     ^          ^          ^
Subreport1 Subreport2 Subreport3
     ^          ^          ^
            Main Report
 

[tulikapuri]

Let's try to clarify. The setup is like this?

  Query1     Query2     Query3
     ^          ^          ^
Subreport1 Subreport2 Subreport3
     ^          ^          ^
            Main Report
 

yess thts right

[Rabbit]

Then the criteria should be set up as:

Between #[Forms]![frmName]![StartDate]# And #[Forms]![frmName]![EndDate]#

... for each of the queries.

  Query1     Query2     Query3
     ^          ^          ^
Subreport1 Subreport2 Subreport3
     ^          ^          ^
            Main Report <--- 3) Open Report
         ^                 ^
Textbox: StartDate   Textbox: EndDate <--- 2) Fill in these fields
                ^
            frmName <--- 1) Open Form
 

[Denburt]

I have tried to follow this and help but I think you need to take a look at table normalisation I do think that relationships between tables are your primary problem.http://www.thescripts.com/forum/thread585228.html

[tulikapuri]

I have tried to follow this and help but I think you need to take a look at table normalisation I do think that relationships between tables are your primary problem.http://www.thescripts.com/forum/thread585228.html

rabbit i followed your instructions in last post and have a form named frmName and have two textbox fields startdate and enddate , i have set the date criterion as Between [Forms]![frmName]![StartDate] And [Forms]![frmName]![EndDate] in all the individual queries.

1. now i open the form frmname
2. enter the startdate and enddate in form.
3. i have a command button on the form to open the main report
4. i use that to get to the main report
5. main report doesnt give me any data, i think everything is working well but the relationships are screwed up somewhere.

How should i set the relationships, if i have Date, TIme, Unit, Operator as common in all the tables.

Thanks,

[Rabbit]

Can you post the following information on each table?

 Table Name
Field Name; Field Type; FK/PK
Field Name; Field Type; FK/PK
Field Name; Field Type; FK/PK
 
 Table Name
Field Name; Field Type; FK/PK
Field Name; Field Type; FK/PK
Field Name; Field Type; FK/PK
 

And also how are the tables related?

[tulikapuri]

What is FK/PK?