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?