Page 1 of 1

Report problem (query)

Posted: Thu Mar 18, 2010 9:24 am
by barbafh3
Hi, im building a report that shows info about a product, like name, serial no, etc, only of those in stock, show the fields on a simple table like this:

Prod Id | Prod Name | Serial no | Entry date | Amount | Type of entry | Show(entry) | User(who made the entry) |

Problem: there are 2 types of entry, one though manual entry and other through request(like from supplier). I need that, if its a manual entry, it show manual entry on type col, and the link to that entry, and if its a request, shows request on type and the link to that resquest. Tables look like this

Product: id, Name, Serial No

Request: id, amount, entry date, product_id, user_id

Manual Entry: id, amount, entry date, product_id, user_id

User: id, nome, username, password

I couldnt find a solution cuz a product can be at the same time on both tables, and many products dont have a serial no, and besides the serial no, i cant see another way, i need help

Re: Report problem (query)

Posted: Thu Mar 18, 2010 9:48 am
by mikosiko
according to your tables descriptions :

- In you table Product you have an ID column
- In your tables ManualEntry and Request you have a Product_id column, therefore a simple join using the ID and Product_id columns should produce the result that you are looking for.
I couldnt find a solution cuz a product can be at the same time on both tables
at the same time in the Manual Entry and Request?... how is that possible? either way the select mentioned before will show you both "Type" columns marked....

Miko

Re: Report problem (query)

Posted: Thu Mar 18, 2010 9:56 am
by barbafh3
this is a simplified version, cuz the tables are big. on the real thing, the serial number has its own table, with product_id for reference, so thats why the product itself can be on manual entry and request, thats what i meant, sorry =/ i can write the real thing here if nedded

Re: Report problem (query)

Posted: Thu Mar 18, 2010 10:01 am
by mikosiko
same answer.... join the tables using product_id and id

Re: Report problem (query)

Posted: Thu Mar 18, 2010 11:09 am
by barbafh3
ill try it here and see if it goes well, soon ill return with the answer

Re: Report problem (query)

Posted: Thu Mar 18, 2010 12:09 pm
by barbafh3
thx man, worked 100%, now i just have to play with the fields to make the report, ty xD