Rabbie
Super Moderator
- Local time
- Today, 15:41
- Joined
- Jul 10, 2007
- Messages
- 5,906
SQL problem within Access
- Thread starter kella101
- Start date
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...
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...
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!!
Any possible solutions would be greatfully accepted!!
DCrake
Remembered
- Local time
- Today, 15:41
- Joined
- Jun 8, 2005
- Messages
- 8,626
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.
Save the query as QryPrices
Then in your queryString change the FROM element of the code to be:
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
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.
Code:
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:
Code:
FROM part_select, part, [B]QryPrices[/B], price_factor, Custom2, CurrencyConUKPThen 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
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.
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.
DCrake
Remembered
- Local time
- Today, 15:41
- Joined
- Jun 8, 2005
- Messages
- 8,626
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
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
Attachments
A dero-what? *Hits google to find out what that means*
Kudo's on your patients tho David
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!!!
