Form Results very Slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:
I have design a Query and Form trying to get an Item Number, his description
and different type of Requirements for the same number.
I don't have problem to get information about it, the results of the Item
Number typed are fine, the problem is because taking around 3 minutes to get
them and also the results are repeating in some of them 8 times and other 4
times.
For example: for the Item Number R12345 I have received 5 different type of
number, then in 3 of them shows me 8 times the same number result and the
other 2 it shows me 4 times of the same number.

I have also forms for the ID and TX of the Item Number and with those I
don't have any problem, after type the number immediately I receive results
on the screen.
This problem is concerning to the RQ.

My question is: What I need to do in order to get results faster that I
receiving now and without repeating the numbers. (4 or 8 Times)?
 
Sounds like you're getting a Cartesian set when Access doesn't clearly
understand which results are directly related. This is either a problem
with your table design or your query.

HTH;

Amy
 
Hi Amy:
Thanks for your response.
Look I know the problem is coming from the Query, I don't know where in
details the problem is.
This is just when I try to get information from the RQ, because from the
other I don't have any problem.

How can I send you my designs in order to review it for yourself, in that
case you can figure out where ir my situation and how I can solve this issue?

Please let me know
And Once again, thank you very much and I hope to be hearing from you soon.

Robby
 
The only real way to get good help with a query problem, is to show the
names of the tables used by the query, and the name of each field in
each of those tables. Also, for each table, you need to clearly show,
what are the primary key field(s) for that table.

For example:

tblCustomer
CustID (PK)
CustName
CustSex
etc.

If you don't know what is a "primary key" - that is the cause of your
problem!

So, I suggest that you show the information stated above, or, do some
research on primary keys and their use in a database system.

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Hi TC:
Thanks for your response...

Look this is the situation...
The keys has two words because I'm working with COBOL program database and
Cobol is using those particulars letters. IM (Item Number), ID
(Identification), RQ
(Requirements), TX (Transaction Number), etc.

This is the list that I have on my TABLES: ID - IM- IM1- ML-MO_STAT - MOS -
MOS1 - OP -RQ - TX... All of them are LINKED_Tables Except the MO_Stat
because
is just a Table.
I have three different forms 1)the ID, 2) Transaction and 3) Requirements.
The first two are working correctly. I’m having problems with the
Requirements. In design view, I have a trust relationship built among: IM,
RQ, OP, MOS, MO_STAT, IM1.
The Im_key is pointing to RQ_Imkey, Im1 (Im_key) is pointing to the
MOS_Imkey, MO_Status is pointing to MOS_Status.
In RQ_satus and OP_Status, I have criteria of “<>Câ€.
In Im_Key I have a Forms formula pointing to the FORM and the name of the
label under FORM.

The Item number is typing into a TEXTBox. and this one look for the correct
number with his information. for example if I type R12345 I need to know the
complete information about that number,
C78170C R 0078999 78 14.07 2 0 20 3/20/2006 R648944
2 3/21/2006 3/20/2006
Where this information corresponding to the Item Number R12345, and is
correct. But in this case this information is repeating 4 and 8 times in a
duplication issue.

And the big problem is the time, because I got the information that I need on
the screen, from the database. Correct Item Number and correct data for this
one.

The IM_KEY is indexed key
The recordsource for the form is: the QryItemNumber.

For the RQ query information the time to get information is around 3
minutes, with the others two queries is just one second.

This is the SQL View of this program. Probably can help...

SELECT IM1.IM_KEY, MO_STAT.MO_DESC_STATUS, RQ.RQ_STATUS, MOS.MOS_JOB,
MOS.MOS_LOT, RQ.RQ_EST_COST, RQ.RQ_QTY_REQ, RQ.RQ_QTY_ISS, RQ.RQ_OP_NUM,
RQ.RQ_DATE_REQ, IM.IM_KEY, [RQ_QTY_REQ]-[RQ_QTY_ISS] AS BAL_REQ,
MOS.MOS_WANTDATE, MOS.MOS_DATE_REL
FROM PD, ((IM INNER JOIN ((IM1 INNER JOIN (RQ INNER JOIN MOS ON
(RQ.RQ_MOSSEQNUM = MOS.MOS_SEQNUM1) AND (RQ.RQ_MOSTYPE = MOS.MOS_TYPE) AND
(RQ.RQ_MOSJOB = MOS.MOS_JOB) AND (RQ.RQ_MOSLOT = MOS.MOS_LOT)) ON IM1.IM_KEY
= MOS.MOS_IMKEY) INNER JOIN OP ON (RQ.RQ_MOSTYPE = OP.OP_MOSTYPE) AND
(RQ.RQ_MOSJOB = OP.OP_MOSJOB) AND (RQ.RQ_MOSLOT = OP.OP_MOSLOT) AND
(RQ.RQ_MOSSEQNUM = OP.OP_MOSSEQNUM)) ON IM.IM_KEY = RQ.RQ_IMKEY) INNER JOIN
MO_STAT ON MOS.MOS_STATUS = MO_STAT.MO_STATUS) INNER JOIN OL ON
MOS.MOS_OLPHKEY = OL.OL_PHKEY
WHERE (((RQ.RQ_STATUS)<>"C") AND
((IM.IM_KEY)=[Forms]![PurchinfoFRM]![ItemNumber]) AND
(([RQ_QTY_REQ]-[RQ_QTY_ISS])>0) AND ((OP.OP_STATUS)<>"C"));

In conclusion I have 2 problems:

1.- Time to get information
2.- Duplication of the Data.

Your help will be greatly appreciated.

I hope to be hearing from you soon.

Robert
 
Robert, I can't help you, unless you document your tables & fields, in
the way that I suggested before.

There is no point discussing your forms or queries, until you have
documented your tables clearly, so that I can check their structure.

Also you have not addressed the issue of "primary keys". As I said
before, you need to state the primary key of each table, OR, research
the meaning & purpose of primary keys in a relational database. I think
that you have not done either!

Please go back & re-read what I said before. I'm happy to help - if you
answer my questions & provide the information I ask for!

TC (MVP Access)
http://tc2.atspace.com
 
Back
Top