469,282 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Report repeating records

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
Apr 4 '07
55 4289
Rabbit
12,516 Expert Mod 8TB
Foreign Key, Primary Key.
Apr 18 '07 #51
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.
Apr 18 '07 #52
Rabbit
12,516 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM [Lab Sand data]
  3. WHERE ((([Lab Sand data].Date) Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate]))
  4. ORDER BY [Lab Sand data].Date, [Lab Sand data].Unit;
The following SQL for Friability of Green Sand :
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM [Friability of Green Sand]
  3. WHERE ((([Friability of Green Sand].Date) Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate]));
The following SQl for LOI Molding Sand:
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM Loimold
  3. 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.
Apr 18 '07 #53
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?
Apr 18 '07 #54
Rabbit
12,516 Expert Mod 8TB
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.
Apr 18 '07 #55
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
Apr 20 '07 #56

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by alhomam | last post: by
4 posts views Thread by zufie | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.