By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 941 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Reading Data from Query and disply it in report

P: 17
Hi All,
I am working on this project which requries making a product by adding one of 7 types of components (each type of component in turn has many variations).
product is made of about 25 components. the purpose of this project is to make a record of each item that has gone into making of each product.
I have created 7 tables for maintaining record of each type of component (which has 23 fields each) and there is a master table which will hold unique ID of each component for about 25 components. inshort each product has 7 x 25 x 23 = 4025+ details.
Now i have to generate a report which should give details of product made from its unique ID selected by user. the report should be for a single product made and not all the products made.
I am using access 2003 Pro with SP2 on Win XP

Now from report i want to run a query which will take input from the product number selected. result of the query should be assigned to text / lable placed in report. i am not able to figure out how to go about reading record data from a query into report.
can you guys help?
thanks !
Mar 14 '07 #1
Share this Question
Share on Google+
23 Replies


Rabbit
Expert Mod 10K+
P: 12,347
I assumed everything is linked through some sort of product ID, you should be able to create a query that selects these product IDs.
Mar 17 '07 #2

P: 17
I assumed everything is linked through some sort of product ID, you should be able to create a query that selects these product IDs.
Hi Rabbit,
I wrote a query which displays correct data but can not read its results into the report. my problem is further compounded as i'll need to read data from atleast 25 queries into this report.

i have decided is to use Dlookup function to carryout this task.
is there a way to loop through the fields of a table without specifying the actual name of the field? say if a table has fields such as

name
lastname
middlename
age
address etc

can i address lastname by '2' or something like it?

thanks for your help.
Regards,
cyclops
Mar 20 '07 #3

NeoPa
Expert Mod 15k+
P: 31,345
That's not how it works Cyclops.
You need to create a query (single query) that your report can run from. Sub-reports can also be used but each individual report needs a single datasource (usually a query) that it is bound to.
DLookup() is not a good idea for building up reports with. That's what queries are for.
Mar 20 '07 #4

P: 17
But NeoPa, my report will need to access multiple queries to get completed. if a report needs a single datasource then my report is not going to be complete.
any alternate method that i should try out?
thanks !
Mar 21 '07 #5

Rabbit
Expert Mod 10K+
P: 12,347
But NeoPa, my report will need to access multiple queries to get completed. if a report needs a single datasource then my report is not going to be complete.
any alternate method that i should try out?
thanks !
Subreports.
Mar 21 '07 #6

NeoPa
Expert Mod 15k+
P: 31,345
Subreports.
... and if that doesn't do the trick then consider multiple separate reports.
But don't forget, if you use a query you can get data from several different sources (tables). Hence 1 report may well work still.
Mar 21 '07 #7

P: 17
... and if that doesn't do the trick then consider multiple separate reports.
But don't forget, if you use a query you can get data from several different sources (tables). Hence 1 report may well work still.
Dear NeoPa & Rabbit,
subforms have solved my problem, thanks a lot for your suggestion !
I have made a form which asks the user to select a product number (from a combo box). this number is stored in a table for the queries on subform (about 25 of them) to access and display the results.
is there an efficient way to pass on the value from combo box to these queries?
thanks & have a nice day !
Cyclops
Mar 26 '07 #8

P: 17
when the VB code writes product number into the table that i have made, it gives an alert message saying "you are about to update 1 row(s)". is there a way to stop it?
thanks,
cyclops
Mar 26 '07 #9

Rabbit
Expert Mod 10K+
P: 12,347
Dear NeoPa & Rabbit,
subforms have solved my problem, thanks a lot for your suggestion !
I have made a form which asks the user to select a product number (from a combo box). this number is stored in a table for the queries on subform (about 25 of them) to access and display the results.
is there an efficient way to pass on the value from combo box to these queries?
thanks & have a nice day !
Cyclops
You can pass a value from an open form to the query by referring to it with the syntax Forms![Form Name]![Control Name]
Mar 26 '07 #10

Rabbit
Expert Mod 10K+
P: 12,347
when the VB code writes product number into the table that i have made, it gives an alert message saying "you are about to update 1 row(s)". is there a way to stop it?
thanks,
cyclops
Surround the part of the code that runs the SQL with:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. ...
  3. DoCmd.SetWarnings True
Mar 26 '07 #11

P: 17
You can pass a value from an open form to the query by referring to it with the syntax Forms![Form Name]![Control Name]
thanks Rabbit for the tip ! i was able to get the required results.
Mar 26 '07 #12

P: 17
Surround the part of the code that runs the SQL with:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. ...
  3. DoCmd.SetWarnings True
once i get the query input direct from the form, this is not requried now. thanks!
Mar 26 '07 #13

P: 17
now i am facing another problem. query that i have written now throws two records whenever the part1 qty is more than 1 where there is actually only one record. the query is as follows.

Expand|Select|Wrap|Line Numbers
  1. SELECT  DISTINCT Component1.*, productdetails.part1Qty
  2. FROM Component1, productdetails
  3. WHERE (Component1.Component1ID)=(SELECT productdetails.[part1] FROM productdetails WHERE (productdetails.[ProductNumber] =Forms!ProductNumber!ProductNo_ComboBox));
  4.  
what could i be doing wrong?
Mar 26 '07 #14

P: 17
now i am facing another problem. query that i have written now throws two records whenever the part1 qty is more than 1 where there is actually only one record. the query is as follows.

Expand|Select|Wrap|Line Numbers
  1. SELECT  DISTINCT Component1.*, productdetails.part1Qty
  2. FROM Component1, productdetails
  3. WHERE (Component1.Component1ID)=(SELECT productdetails.[part1] FROM productdetails WHERE (productdetails.[ProductNumber] =Forms!ProductNumber!ProductNo_ComboBox));
  4.  
what could i be doing wrong?
I think i got it. i should have created an inner join like this.


Expand|Select|Wrap|Line Numbers
  1. SELECT  DISTINCT Component1.*, productdetails.part1Qty
  2. FROM productdetails INNER JOIN Component1 ON productdetails.part1 = Component1.Component1ID
  3. WHERE (Component1.Component1ID)=(SELECT productdetails.[part1] FROM productdetails WHERE (productdetails.[ProductNumber] =Forms!ProductNumber!ProductNo_ComboBox));
  4.  
am i correct ?
Mar 26 '07 #15

P: 17
No, this does not seem to work. can u help?
thanks
Mar 26 '07 #16

NeoPa
Expert Mod 15k+
P: 31,345
You are on the right lines, but to know precisely what you need, one would have to know how your data (tables) fit together.
Try posting the MetaData for both tables.
Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Mar 26 '07 #17

Rabbit
Expert Mod 10K+
P: 12,347
Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT  DISTINCT Component1.*, productdetails.part1Qty
  2. FROM productdetails INNER JOIN Component1 ON productdetails.part1 = Component1.Component1ID
  3. WHERE (productdetails.[ProductNumber] =Forms!ProductNumber!ProductNo_ComboBox);
  4.  
Mar 26 '07 #18

P: 17
You are on the right lines, but to know precisely what you need, one would have to know how your data (tables) fit together.
Try posting the MetaData for both tables.
Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time

here is the MetaData (partial)
table name =component1
Expand|Select|Wrap|Line Numbers
  1. component1ID; Autonumber
  2. Length; Integer
  3. Width; Integer
  4. Thickness; Integer
  5. Strength; Numeric
table name = productdetails
Expand|Select|Wrap|Line Numbers
  1. productID;autonumber
  2. productnumber;string;indexkey
  3. part1;Numeric
  4. part2;Numeric
etc.

i think i have got the solution...
Mar 28 '07 #19

P: 17
i have modified the query like this..

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Component1.*, productdetails.part1Qty
  2. FROM Component1, productdetails 
  3. WHERE (((Component1.Component1ID)=(SELECT productdetails.[part1] FROM productdetails 
  4. WHERE (productetails.[ProductNumber] = Forms!ProductNumber!ProductNo_ComboBox))) 
  5. AND ((productdetails.ProductNumber)=[Forms]![ProductNumber]![ProductNo_ComboBox]));
this has worked !

now i have next problem.... i hope i am not bothering you all too much ;-)
Mar 28 '07 #20

P: 17
I have now a report with about 25 subreports into it. If query from one of the subreports gives no result (ie. that part is not used for the product) then on main report it leaves a blank.
I do not want this bank to appear. The subreports below it should move up such that the report does not indicate in any way that there could have been a part here which is not used.
Any thoughts? Will share with you the solution as soon as I find one !
thanks & have a nice day !
Mar 28 '07 #21

P: 17
I have now a report with about 25 subreports into it. If query from one of the subreports gives no result (ie. that part is not used for the product) then on main report it leaves a blank.
I do not want this bank to appear. The subreports below it should move up such that the report does not indicate in any way that there could have been a part here which is not used.
Any thoughts? Will share with you the solution as soon as I find one !
thanks & have a nice day !
ha ha ha ! sometimes the solution is simple but one makes it complicated (atleast i did it!).
i just had to make the CanShrink Property of all subreports to Yes from default No and it did the trick !
Thanks a lot for your time! will get back to you incase i get stuck somewhere else.
Regards,
Cyclops
Mar 28 '07 #22

NeoPa
Expert Mod 15k+
P: 31,345
Well, this is a turn-up for the books. Not only do we have an OP with multiple questions in this thread, but we also has one that's providing all the answers too. Obviously no impairment of his sight - Eh Cyclops?
With your responsive replies I would have been happy to offer further help but it seems it is no longer necessary (In such a short time too :D)
Mar 28 '07 #23

P: 17
Well, this is a turn-up for the books. Not only do we have an OP with multiple questions in this thread, but we also has one that's providing all the answers too. Obviously no impairment of his sight - Eh Cyclops?
With your responsive replies I would have been happy to offer further help but it seems it is no longer necessary (In such a short time too :D)

Hey NeoPa, thanks for your offer... I will save it for future ;-)
Mar 29 '07 #24

Post your reply

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