Return Description value realting to PO table value

[shabbaranks]

Hi,

This one is really puzzling me and I cant see why I cant get the values to match. I have 3 tables joined as attached and Im trying to use the PO from the PO_Detail table to display the Description from the Material_Req Table. The two values are linked as the description in the Material_Req Table is for the PO in the PO_Detail table but I just cant get the results to show this.

Can anyone help me please?
Thanks

 

[namliam]

Without seeing the actual data, it is a complete stab in the dark...

Perhaps join on another field?

 

[shabbaranks]

Ive added a sample of the database changing any confidential info. What I am trying to get is the PO numbers 3864,3872,3882,3886,3892 and 3893 to show their descriptions from the material_req table.

Just to note Im not asking you to do it, but if you could explain based on these tables how I should be creating the query that would be fantastic - thanks.

 

[namliam]

You are trying to get description from the Material Req table right?

If you follow the trail in the Source table, you see that PO_Details 3892 and 3893 dont even have any material listed and the other two material's dont have a match:
M10 DORMER E344
R116.1897.02 TN35

Your joins seem to work just fine, just your data isnt "there"

 

[shabbaranks]

Thanks, it would seem that some of the descriptions are stored within the Material_Req.Description and some are stored within the Source.Description.

I've managed to create a query which picks up the description from the Source.Description and also queries a separate query which holds any description information stored within the Material_Req table which refers to the PO from the PO_Detail Table.

This is fine but I was wondering if the method is correct - Ive attached the working example. If you run the CombineAll_Qry you'll see that some values from one table are blank but are stored within the corresponding table. And some even hold both values.

These tables are part of a third party application to which I have exported and then imported into Access to try and work out the joins.

My next task is to take the query code and attempt to use that within crystal reports hopefully using the SQL code

 

[namliam]

SQL <> SQL
The S is Structured not Standard, while a lot of SQL is the same/simular, I wouldnt expect it to be 100% usable and interexchangable with any other language.

If you need "Group by" on each of your queries to make it work there is something wrong, didnt check if you do or dont need them... but turn them off by default

You can also use something like:
Nz([source].[Description],[materialdescript_qry].[description])

To "only" have one column filled with which ever of the two is filled. In this case if both are filled it will take the description from the Source table.

 

[shabbaranks]

Thanks for the Nz tip - worked a treat. Although it is a false errand as I need to get this into crystal reports, but it's helped.

Also when you say about SQL <> SQL Im not too sure what you mean? You can create SQL queries within Crystl Reports - are you referring to the fact the SQL context is created within the Access query builder?

Thanks

 

[namliam]

I am saying that while SQL seems to be relatively standard, in fact it is not....
SQL you generate in one database is by no means guaranteed to work in another....

 

[shabbaranks]

Ah ok thanks, that's what I thought you meant but I can never be too sure

Surprisingly with a little bit of adjustment I think it should be ok