Reading Data from Query and disply it in report

[cyclops]

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 !

[Rabbit]

I assumed everything is linked through some sort of product ID, you should be able to create a query that selects these product IDs.

[cyclops]

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

[NeoPa]

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.

[cyclops]

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 !

[Rabbit]

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.

[NeoPa]

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.

[cyclops]

... 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

[cyclops]

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

[Rabbit]

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]

[Rabbit]

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:

DoCmd.SetWarnings False
...
DoCmd.SetWarnings True

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

thanks Rabbit for the tip ! i was able to get the required results.

[cyclops]

Surround the part of the code that runs the SQL with:

DoCmd.SetWarnings False
...
DoCmd.SetWarnings True

once i get the query input direct from the form, this is not requried now. thanks!

[cyclops]

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.

SELECT  DISTINCT Component1.*, productdetails.part1Qty
FROM Component1, productdetails
WHERE (Component1.Component1ID)=(SELECT productdetails.[part1] FROM productdetails WHERE (productdetails.[ProductNumber] =Forms!ProductNumber!ProductNo_ComboBox));
 

what could i be doing wrong?

[cyclops]

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.

SELECT  DISTINCT Component1.*, productdetails.part1Qty
FROM Component1, productdetails
WHERE (Component1.Component1ID)=(SELECT productdetails.[part1] FROM productdetails WHERE (productdetails.[ProductNumber] =Forms!ProductNumber!ProductNo_ComboBox));
 

what could i be doing wrong?

I think i got it. i should have created an inner join like this.

SELECT  DISTINCT Component1.*, productdetails.part1Qty
FROM productdetails INNER JOIN Component1 ON productdetails.part1 = Component1.Component1ID
WHERE (Component1.Component1ID)=(SELECT productdetails.[part1] FROM productdetails WHERE (productdetails.[ProductNumber] =Forms!ProductNumber!ProductNo_ComboBox));
 

am i correct ?

[cyclops]

No, this does not seem to work. can u help?
thanks

[NeoPa]

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

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[Rabbit]

Try:

SELECT  DISTINCT Component1.*, productdetails.part1Qty
FROM productdetails INNER JOIN Component1 ON productdetails.part1 = Component1.Component1ID
WHERE (productdetails.[ProductNumber] =Forms!ProductNumber!ProductNo_ComboBox);
 

[cyclops]

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

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

here is the MetaData (partial)
table name =component1

component1ID; Autonumber
Length; Integer
Width; Integer
Thickness; Integer
Strength; Numeric

table name = productdetails

productID;autonumber
productnumber;string;indexkey
part1;Numeric
part2;Numeric

etc.

i think i have got the solution...

[cyclops]

i have modified the query like this..

SELECT DISTINCT Component1.*, productdetails.part1Qty
FROM Component1, productdetails 
WHERE (((Component1.Component1ID)=(SELECT productdetails.[part1] FROM productdetails 
WHERE (productetails.[ProductNumber] = Forms!ProductNumber!ProductNo_ComboBox))) 
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 ;-)

[cyclops]

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 !

[cyclops]

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

[NeoPa]

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)

[cyclops]

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 ;-)