Report repeating records

[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?

[b]Table Name[/b]
Field Name; Field Type; FK/PK
Field Name; Field Type; FK/PK
Field Name; Field Type; FK/PK

[b]Table Name[/b]
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?

[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