SQL problem within Access

[Rabbie]

hey guys... i have tried debugging it, and putting it in a query window, and it comes up wit the same error -
"Syntax error - (missing operator) in query expression 'part.id = part_select.part
AND fixed_price_list.part_id=part.id
AND Custom2.JDE_Price_Group=price_factor.customer_group
<b>AND price_factor.part_group = part.prie_group
AND ficed_price_list.nationality= 823
AND Custom2.Customer_Name = 'A'.

That is the entire error message i recieve...!??!?!

The bit I highlighted looks as if it may be typed in wrong. It is so easy to mistype

 

[kella101]

hi that was just a mistype in my post, not the code, coz i couldnt copy and paste the error message so had to simply re write it.

 

[namliam]

Hmz,

I just presumed this would work... but it appearently (after actually testing it) doesnt...

You dont seem to be able to use a union query in an insert into statement , my bad

I guess you have to make it a big join then... joining all the tables in and indeed using an iif to pick up the proper fields...

 

[DCrake]

Simple Software Solutions

Kella

Can I have a progress report please. As I may have a solution for you.

David

 

[kella101]

Hi david, my progress is minimal, after much testing found that i cannot use the union in the way i need. I tried using a very big if statement written in the vb code, but have yet to get it to work.

Any possible solutions would be greatfully accepted!!

 

[namliam]

How about if you just join both the tables you need... then use an IIF around your formula's

 

[kella101]

hey, iv never wrote a join before, would i do an inner join? wat would be the syntax? how does a join work? and as of yet i have not seen an iif work. :S

 

[namliam]

Yes you have...

Join is just another way of taking care of "connecting" tables like you are doing in your where clause...

 

[kella101]

ah thanks, ill give it a go and get back to you!,

cheers!

 

[DCrake]

Simple Software Solutions

Kella.

Here goes....

The main issue is the Factor being a 1 or a 0 correct. And based on this is the decision as to use the fixed_price_list or the price_list tables in our query sql.

It looks like you have two very similar, if not identical tables. So wih that in mind lets create a union query of these two tables.

SELECT Price As [Base Price], 0 As [FixedPrice], Part_id As [ID], Min_Quantity As [Quantity Required], Nationality, "P" As Source FROM Price_List

UNION ALL SELECT 0 As [Base Price], Price As [FixedPrice], Part_ID As [ID], Min_Quantity As [Quantity Required], Nationality, "F" As Source FROM Fixed_Price_List

ORDER BY ID;

Save the query as QryPrices

Then in your queryString change the FROM element of the code to be:

FROM part_select, part, [B]QryPrices[/B], price_factor, Custom2, CurrencyConUKP

Then in the remainder of the query refer to the fields via the QryPrices alias. This should entail you get the right info.

The N and F are just a flag to show which table is priving the data.

You will still need to employ the Iff() statements.

But refer to either the Price or the FixedPrice field to obtain the correct value.

By putting the union query outside the main sql and referring to it later makes the coding easier.

Good Luck

David

 

[kella101]

Hi David,
I need to use an "INSERT INTO..." which you cant use with a "UNION" :S

 

[namliam]

Perhaps you can if you use something like David did... he is not using the UNION in the insert into statement but rather in the subquery that he is joining to your query.

More or less doing in the subquery which I was proposing doing in the one query...

I dont think this is the best solution tho, it might just work.

 

[kella101]

i am sorry but i dont understand how i add the union as a subquery, all i know is how to extend the query " & _ " or query = query &...

 

[namliam]

But David told you... step by step... shouldnt be a problem?

 

[DCrake]

Simple Software Solutions

Kella.

It seams you knowledge on Access is less than I envisiaged, this is not a derogitory comment, just an observation.

If you create a new query but don't select a table from the list the top pane is blank and the toolbar only offers you two options SQL and Datasheet. Click on the SQL button (diag 1)

Next enter in your SQL for the union query in the SQL pane (Diag 2)

When done ckick the same icon to view in a datasheet mode to test the results.

If happy then save the query.

Then in your main sql string follow the directors as per my previous post.

David

 

[namliam]

this is not a derogitory comment

A dero-what? *Hits google to find out what that means*

Kudo's on your patients tho David

 

[kella101]

Hey thanks alot guys, even i knew what derogatory meant lol. I tried to tell you guys this is only my second week using MS Access, so its all currently stil alot of trial and error. Very greatful for your patients. Our systems our down this afternoon tho, so i am unable to try these comments until monday. will post how far i get. Thanks again!!!

 

[DCrake]

Simple Software Solutions

Namliam

In England we have a thing call patience, as in, persistance, staying power, tolerance and fortitude. We also have a lot of patients, as in Private and National Health.

David

 

[namliam]

lol ok The Patients have to be Patient ?? Or something ... LOL

Patience it is... must have been funny for you to read that

 

[kella101]

These are some interesting posts guys lol. Iv been back in newcastle all weekend, and now we are still having problems with our servers, so still unable to test.