Replace one set of record for another in a query.

[Basshopper]

I have a BOM table and a Alternative Parts BOM Table

In the Alt Parts table I have a yes/no to "use part" my BOM query needs to take an alt parts row(including about 12 fields) and replace one of the rows in the BOM with a row from the alt parts bom table. Both tables have the same Line # as a field to match up. Any suggestions to get this to happen. IIF statement would work but what command to replace the rows of records???

thanks for your help

 

[flyover789]

you could try a union:

SELECT T1.field1, T1.field2, T1.field3 [COLOR=green]'list the fields to be showed[/color]
FROM tbl_BOM T1 LEFT JOIN tbl_alt_BOM T2 
ON T1.LineId = T2.LineId 
WHERE T2.[use part] = 0 or T2.[use part] is null
UNION
SELECT T3.field1, T3.field2, T3.field3 [COLOR=green]'list the fields to be showed[/color]
FROM tbl_alt_BOM T3
WHERE T2.[use part] = -1;

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.com

 

[flyover789]

I should change my nick into TheTypoMan

here is the corrected sql:

SELECT T1.field1, T1.field2, T1.field3 [COLOR=green]'list the fields to be showed[/color]
FROM tbl_BOM T1 LEFT JOIN tbl_alt_BOM T2 
ON T1.LineId = T2.LineId 
WHERE T2.[use part] = 0 or T2.[use part] is null
UNION
SELECT T3.field1, T3.field2, T3.field3 [COLOR=green]'list the fields to be showed[/color]
FROM tbl_alt_BOM T3
WHERE T3.[use part] = -1;

Martin Serra Jr.

http://www.mserrasystems.com

 

[Basshopper]

Martin: can I add this to an existing query or do I put this in the Criteria row? of my query?

thanks

Larry

 

[flyover789]

this is the complete sql of a new query.
create a new query and paste the code into the sql pane.

you have to adapt the code to your field- and tablenames to get it to work.

let me know, if you need more help.

Martin

Martin Serra Jr.

http://www.mserrasystems.com

 

[Basshopper]

Hi Martin: Got it working except for 2 items.

1) the new line is showing up but I need the old line of records to disappear, how do I make them go away.

2) both tables do not have the same # of fields in them can I put in Nulls in for extra fields and do I put them in the alt parts table or T2 in same order as the T1. Also the use part is only in the T2 alt parts table not in the T1 table

thanks

Larry

 

[flyover789]

Hi again,

can you please:
1. post the SQL you have so far
2. post the tablenames and
3. post the fieldnames of each table in their original order

I'll try to adapt the SQL so that it works for you.

you can also email me your mdb (or at least a mdb with the two tables) and I'll try to do it for you there.
contact@mserrasystems.de

Kind regards,
fly

Martin Serra Jr.

http://www.mserrasystems.com

 

[Basshopper]

Hi Martin: Thanks for looking my database over,Got your updated Code and it worked great thanks for your help with this.

 

[flyover789]

just some more explanations, why at first the old line didn't disappear:

The problem we had with the first solution is, that the Line# in the tbl_alt_BOM are not unique, so e.g. the line# 1 appear twice in the table, once with usepart checked and once with usepart unchecked …

Final solution:
The new union query qry_UNION_QUOTE_BOM_USEPART is based on two subqueries, of which one selects the lines#’s from the tbl_alt_BOM, that are flagged as use and are also in tbl_BOM. The second one selects all the rest of tbl_BOM, that is not in the first query.
The union query unites the two queries.

Greetings,
fly





Martin Serra Jr.

http://www.mserrasystems.com