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]

Foreign Key, Primary Key.

[tulikapuri]

Table 1. Daily Sand Lab

Field Name; Field Type; PK/FK
ID ; Autonumber; PK
Date; Date/Time;
Time; Date/Time;
Unit; Text;
Operator ; Text;
Temp;. Text;
Mois. ; Text;
MB ; Text;
MB % ; Text;
Compactibility; Text;
GS ; Text;
Perm ; Text;
Sp. Wt ; Text;

Table 2. Friability of Green Sand

Field Name; Field Type ; PK/FK
ID ; Autonumber; PK
Date ; Date/Time ;
Time; Date/Time ;
Unit ; Text;
Operator; Text;
% Mois ; Text;
MB Clay; Text;
Comp% ; Text;
Total Sp. Wt; Text;
Wt. of Sand in pan; Text;
% Friability ; Text;

Table 3. LOI Molding Sand
Field Name ; Field Type ; PK/FK
ID ; Autonumber ; PK
Date ; Date/Time ;
Time ; Date/Time ;
Unit ; Text;
Operator ; Text;
Core # ; Text;
Sample Wt. ; Text;
Gross Wt. ; Text;
Tare Wt. ; Text;
After Furnace wt. ; Text;
% LOI ; Text;

Query 1
It relates all the above tables by Date and ID.

In case you have any other questions, please ask me.

[Rabbit]

I don't see any relation between your tables.

Is the following the queries you're using now?
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 [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate]))
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 [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate]));

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 [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate]));

You also might want to think about renaming your Date and Time variables to something else as right now it's using reserved Access words.

[tulikapuri]

Yess, these are the SQL's for the tables Sand Lab Data, Friability of Green Sand and Loi Molding Sand.

What do i change the time and date variables as , and why do we need to change them, what relationships are required?

[Rabbit]

Yess, these are the SQL's for the tables Sand Lab Data, Friability of Green Sand and Loi Molding Sand.

What do i change the time and date variables as , and why do we need to change them, what relationships are required?

Relationships aren't required. It just seems to me that you are creating 3 very different reports whose only common factor is the time period and I was wondering if there was anything beyond that.

Date and Time are functions within Access and as such, if you name anything after one of those functions, it can cause problems later.

If indeed those are your SQL and you've done everything as I laid out, then I am stumped to as why it's still asking you to define your parameters 6 times. The only remote possibility I could think of was the fact that your fields are named after reserved words in Access.

[tulikapuri]

Relationships aren't required. It just seems to me that you are creating 3 very different reports whose only common factor is the time period and I was wondering if there was anything beyond that.

Date and Time are functions within Access and as such, if you name anything after one of those functions, it can cause problems later.

If indeed those are your SQL and you've done everything as I laid out, then I am stumped to as why it's still asking you to define your parameters 6 times. The only remote possibility I could think of was the fact that your fields are named after reserved words in Access.

I dont know, i am already running out in time becuase of the deadline of this project and i dont know how i can really sort it out now :(, nothing is working, i think we should have a provision in this wepage to exchange DB's as it will make life more easier.

Do u have any other email address where i can zip it to you becuase if you just review it once i am sure something will work out.

Thanks ,
Tulika