correct SQL statement? ...

[vbahelp07]

Run-time error ...
no value given one or more required paramete

from:

UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");

basically i need to create an update query to update the OptionCombo field where:
1. OptionCategory=BODY
2. Combine all the BODY OptionItems as one into the OptionCombo field.
3. For each InvoiceNumber AND GuitarItem.

NOTE: some invoices (guitaritems) may not have a BODY Option_Item, this can place "N" in the OptionCombo BUT only if the invoice does not have a single BODY Option_Item, if it does, it needs to put the combined BODY Option_Item values into the OptionCombo field.

How should I rewrite the SQL to achieve this with the error.

 

[vbahelp07]

this is what the result should be after the UPDATE is ran:

InvoiceDate	InvoiceNumber	GuitarItem	Option_Item	OptionCategory	OptionCombo
9/7/07	2365186	AE185	38	Body	38 BB
9/7/07	2365186	AE185	AB	Neck	38 BB
9/7/07	2365186	AE185	ABL	Options	38 BB
9/7/07	2365186	AE185	BB	Body	38 BB
9/7/07	2365186	AE185	DTS	Finishes	38 BB
9/7/07	2365186	AE185	G	Options	38 BB
9/7/07	2365186	AE185	QPH	Headstock	38 BB
9/7/07	2365186	AE185	QSSA	Finishes	38 BB
9/7/07	2365186	AE185	STF	Neck	38 BB
9/7/07	2365186	AE185	TN	Neck	38 BB
9/7/07	2365203	AE185	38	Body	38
9/7/07	2365203	AE185	CTH	Headstock	38
9/7/07	2365203	AE185	FPH	Headstock	38
9/7/07	2365203	AE185	FSKA	Finishes	38
9/7/07	2365203	AE185	NIN	Neck	38
9/7/07	2365203	AE185	SL	Options	38
9/6/07	2364987	AE185	BC	Options	N
9/6/07	2364987	AE185	FPH	Headstock	N
9/6/07	2364987	AE185	FTCA	Finishes	N

or should I leave the OptionCombo field blank for the records that are not Body OptionCategory instead?

 

[lameid]

I can't extrapolate enough of the tables to figure out what you are asking...

What tables are involved?

Which one is the recordsource for the form and which one is the rowsource of the combobox (guessing you have one form option combo)?

What table is being updated with what information?

That may not be enough information, we may need to know what the information looks like before the update.

Finally, there is no Concatenate function. However the string you have as its parameter is an example of concatenation.

 

[vbahelp07]

Hi lameid ( Lame ID?? LOL!)

ok,
1. GuitarHeader table has InvoiceDate, InvoiceNumber, GuitarItem, and OptionCombo (basically)
2. GuitarDeatils table has InvoiceNumber and Option_Item
3. FinishOptions table has OptionItem and OptionCateogry (basically)

the 3 linked tables query is the GuitarOptionDetails which lists: as seen in the result above.
InvoiceDate
InvoiceNumber
GuitarItem
Option_Item
OptionCategory
OptionCombo

need to update the OptionCombo field that resides in the GuitarHeader table of all the Option_Items concatenated (using the current Concatenate() function I found that DHookom has posted).

need help with the SQL statement that goes in the Concatenate() in the query to do this.

so i'm trying to tell it to combine all of the Option_items into the OptionCombo field where InvoiceNumber and GuitarItem changes for only when the OptionCategory = Body.

trying to show the example of the update if ran in the result provided.

NOTE: the last invoice in the set has "N" as the OptionCombo BECAUSE this invoice has NO Body Option_items.

Hope that made better sense??

 

[vbahelp07]

UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT OptionCombo FROM GuitarOptionDetails WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");

??

 

[vbahelp07]

more something like:

IIf([OptionCategory]="Body",Concatenate("SELECT OptionCombo FROM GuitarOptionDetails WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """"),"N")

??

 

[vbahelp07]

it's not write, but something like that.

can you help fix the statement there?

 

[lameid]

First I am not familiar with Dhookom's concatenate function so I am going to assume it takes an SQL statement with one field and contatenates the list.

That said I do not think your SQL statement you are concatenating is valid nor is your update statement.

Based on your table layout OptionCombo is in your GuitarHeader table...

Update GuitarHeader Set GuitarHeader.optionCombo = <stuff>

You need to limit what you update to stuff that has a body or FinishOptions.OptionCategory = "Body"

Update GuitarHeader Set GuitarHeader.optionCombo = <stuff>
Where GuitarHeader.InvoiceNumber IN 
     (Select GuitarDeatils.InvoiceNumber 
      From GuitarDeatils inner Join FinsihOption ON GuitarDeatils.Option_Item = FinsihOption.OptionItem
      Where GuitarDeatils.FinishOption.OptionCategory = "Body")

Stuff needs to be the list of something as defined by what I assume concatenate does above.

Update GuitarHeader Set GuitarHeader.optionCombo = Concatenate("Select GuitarDeatils.Option_Item
      From GuitarDeatils inner Join FinsihOption ON GuitarDeatils.Option_Item = FinsihOption.OptionItem
      Where GuitarDeatils.FinishOption.OptionCategory = ""Body"" AND GuitarDeatils.InvoiceNumber = " & 
      GuitarHeader.InvoiceNumber)
Where GuitarHeader.InvoiceNumber IN 
     (Select GuitarDeatils.InvoiceNumber 
      From GuitarDeatils inner Join FinsihOption ON GuitarDeatils.Option_Item = FinsihOption.OptionItem
      Where GuitarDeatils.FinishOption.OptionCategory = "Body")

 

[vbahelp07]

Thanks.

i still get the
Run-time error ...
No value given for one ore more required parameter

possibly due the the Null values where there are no Body options for some invoices??

also, i'd like to put an IIF statement

i was thinking
UPDATE OptioNCombo ...
IIf([OptionCategory]="Body",Concatenate(sql here),"N")

so that for the invoices without ANY Body Options will have "N" for none or "None", really doesn't matter. just some value so that it can link back with some value.


thank you kindly!!

 

[vbahelp07]

btw - clicked through the run-time error to the end.

it said there were 130 records but it could not update any due to key violations so nothing basically updated values into the OptionCombo field.


you've been great. do let me know if i need to change the way i am asking or providing info ...

 

[vbahelp07]

ok, i did this and am getting weird results:

I'm using:

IIf([OptionCategory]="Body",Concatenate("SELECT Option_Item FROM GuitarOptionDetails WHERE GuitarItem & Option_Item =""" & GuitarOptionDetails.GuitarItem & GuitarOptionDetails.Option_Item & """"),"N")

and the result is this:

InvoiceDate	InvoiceNumber	GuitarItem	Option_Item	OptionCategory	OptionCombo
10/18/07	2372490	CT3M	B	Finishes	N
10/18/07	2372490	CT3M	BC	Options	N
10/18/07	2372559	AE185	38	Body	N
10/18/07	2372559	AE185	BC	Options	N
10/18/07	2372559	AE185	DTS	Finishes	N
10/18/07	2372559	AE185	GL	Options	N
10/18/07	2372559	AE185	IN	Headstock	N
10/18/07	2372559	AE185	NIN	Neck	N
10/18/07	2372559	AE185	QPH	Headstock	N
10/18/07	2372559	AE185	QSKA	Finishes	N
10/18/07	2372559	AE185	R14	Neck	N
10/18/07	2372559	AE185	SL	Options	N
10/18/07	2372559	AE185	STJF	Neck	N
10/18/07	2372559	AE185	TN	Neck	N
10/15/07	2371872	LB76P	5M	Neck	SP2P
10/15/07	2371872	LB76P	BC	Options	SP2P
10/15/07	2371872	LB76P	BMF	Neck	SP2P
10/15/07	2371872	LB76P	KOA	Woods	SP2P
10/15/07	2371872	LB76P	SP2P	Body	SP2P
10/15/07	2371884	B4	BST	Finishes	HB2 HB2
10/15/07	2371884	B4	HB2	Body	HB2 HB2
10/11/07	2371330	SC90M	5W	Neck	RB RB RB
10/11/07	2371330	SC90M	5W	Body	RB RB RB
10/11/07	2371330	SC90M	B	Finishes	RB RB RB
10/11/07	2371330	SC90M	BC	Options	RB RB RB
10/11/07	2371330	SC90M	CG	Finishes	RB RB RB
10/11/07	2371330	SC90M	NIN	Neck	RB RB RB
10/11/07	2371330	SC90M	RB	Body	RB RB RB

so in these 2 invoices the 1st one is correct, "N" because there's no Body optioncategory.
But for the 2nd invoice "2372559" there is one Body option_item "38". It should have put "38" in the OptionCombo field instead put "N".
And still the 3rd invoice has "SP2P" in OptionCombo, which is correct since that's the ONLY Body option_item for this invoice.
And still the 4th invoice why it has "HB2 HB2" instead of just "HB2"?
And the last one shown here, why does it only have "RB RB RB" instead of "5W RB"?

so I changed it to:
IIf([OptionCategory]="Body",Concatenate("SELECT Option_Item FROM GuitarOptionDetails WHERE InvoiceNumber & GuitarItem & Option_Item =""" & GuitarOptionDetails.InvoiceNumber & GuitarOptionDetails.GuitarItem & GuitarOptionDetails.Option_Item & """"),"N")

and it's still not correct, although now it's not combing none of the values.

what's wrong?

 

[vbahelp07]

this query concatenates ALL the Option_items, how to change it so it'll be for only the Option_Items that are Body OptionCategory and place "N" when there are no Body OptionCategory per invoice?

UPDATE GuitarHeader SET GuitarHeader.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarOptionDetails WHERE InvoiceNumber & GuitarItem =""" & GuitarHeader.InvoiceNumber & GuitarHeader.GuitarItem & """");

or
this puts the "N" when there is no Body Option_Item of the OptionCategory but still combines ALL the Option_Items and NOT just the ones that are from the Body OptionCategory.

UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = IIf([OptionCategory]="Body",Concatenate("SELECT Option_Item FROM GuitarOptionDetails WHERE InvoiceNumber & GuitarItem =""" & [GuitarHeader].[InvoiceNumber] & [GuitarHeader].[GuitarItem] & """"),"N")
WHERE (((GuitarOptionDetails.OptionCategory)="Body"));

between the two, queries, how can I combine/change it to do
What it should be:

GuitarHeader	GuitarHeader	GuitarHeader	GuitarDetails	FinishOptions	GuitarHeader	<-- tables fields are from
InvoiceDate	InvoiceNumber	GuitarItem	Option_Item	OptionCategory	OptionCombo	
9/7/07	2365186	AE185	38	Body	38 BB	
9/7/07	2365186	AE185	AB	Neck	38 BB	
9/7/07	2365186	AE185	ABL	Options	38 BB	
9/7/07	2365186	AE185	BB	Body	38 BB	
9/7/07	2365186	AE185	DTS	Finishes	38 BB	
9/7/07	2365186	AE185	G	Options	38 BB	
9/7/07	2365186	AE185	QPH	Headstock	38 BB	
9/7/07	2365186	AE185	QSSA	Finishes	38 BB	
9/7/07	2365186	AE185	STF	Neck	38 BB	
9/7/07	2365186	AE185	TN	Neck	38 BB	
9/7/07	2365203	AE185	38	Body	38	
9/7/07	2365203	AE185	CTH	Headstock	38	
9/7/07	2365203	AE185	FPH	Headstock	38	
9/7/07	2365203	AE185	FSKA	Finishes	38	
9/7/07	2365203	AE185	NIN	Neck	38
9/7/07	2365203	AE185	SL	Options	38
9/6/07	2364987	AE185	BC	Options	N
9/6/07	2364987	AE185	FPH	Headstock	N
9/6/07	2364987	AE185	FTCA	Finishes	N

thank you!

 

[lespaul]

[off topic]
TGML tag assistance. You can use the [ignore][tt][/ignore] tag to provide your table layout:

[tt]
Column1 Column2 Column3
data data data
data data data
data data data[/tt][/off topic]

Why don't you provide some sample data from your tables (GuitarHeader, GuitarDetails and Finish Options) and then exactly what results you want from that sample. You've posted your results from data that we can't see, and it's difficult to extrapolate....if we can also see the data it makes it much easier to visualize and assist.

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[lameid]

I think the error you are getting is from the concatenate function.

Unless I see it, I can't tell.

As for your key violations, check to see if optioncombo has a unique index and if so remove it and strongly consider any validation rules and relationships. You did get it to update so I'm not sure what is wrong here or maybe you fixed it.

You can't use and IIF function like this because some values have a body and something other than a body. So depending on which it updates based on last you may get different or odd results. I recommend my original query and if a simple NZ does not work for reporting, I would modify my second example to say Not In instead of IN.

Also if you try something and a different solution has been recommended, it is helpful to post your entire SQL so we know what you are running or what was wrong with the solution provided.

At this point, I need to know what if anything was wrong with what I posted aside from updating to 'N' and see the concatenate code.

At the top of each thread or faq is the appropriate word followed by a number. If you copy and paste that it, it creates a link to it.

Lespaul's suggestion for data in the individual underlying tables is good too.

 

[vbahelp07]

hi to both!
thank you. i will once I get a chance.

we have to pack and be ready to evacuate Poway, CA

the fires have gotten worse due to the strong winds and it's close. they have evacuated Rancho Bernardo which is just our neighboring city.

 

[lespaul]

well, saving your life is a much more important task than figuring this out....get the he!! out of there!!

We'll be here when you get back!

Leslie

 

[vbahelp07]

Hi Leslie,
we're at a friend's ... it's worse then the 2003 fires. we're just waiting to see what happens.

so, while waiting, will try to get the resolved.

UPDATE GuitarHeader SET GuitarHeader.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarOptionDetails WHERE OptionCategory=""Body"" & InvoiceNumber & GuitarItem =""" & GuitarHeader.InvoiceNumber & GuitarHeader.GuitarItem & """");

the result of this is doing nothing.

I take out the OptionCategory=""Body"" and the results for this is:

UPDATE GuitarHeader SET GuitarHeader.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarOptionDetails WHERE InvoiceNumber & GuitarItem =""" & GuitarHeader.InvoiceNumber & GuitarHeader.GuitarItem & """");

InvoiceDate	InvoiceNumber	GuitarItem	Option_Item	OptionCategory	OptionCombo
10/18/07	2372490	CT3M	B	Finishes	B BC
10/18/07	2372490	CT3M	BC	Options	B BC
10/18/07	2372559	AE185	38	Body	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	BC	Options	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	DTS	Finishes	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	GL	Options	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	IN	Headstock	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	NIN	Neck	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	QPH	Headstock	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	QSKA	Finishes	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	R14	Neck	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	SL	Options	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	STJF	Neck	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	TN	Neck	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS
10/18/07	2372559	AE185	UCS	Options	38 BC DTS GL IN NIN QPH QSKA R14 SL STJF TN UCS

which is exactly what the query is saying to do, concatenate the Option_Items into the OptionCombo.

How do I change it so it's ONLY for the Body OptionCategory type and put N when the invoice has NO Body OptionCategory.

this:

IIf([OptionCategory]="Body",Concatenate("SELECT Option_Item FROM GuitarOptionDetails WHERE InvoiceNumber & GuitarItem =""" & GuitarHeader.InvoiceNumber & GuitarHeader.GuitarItem & """"),"N")

is in consistent. it's correct on some and incorrect in others.


So i'm thinking it's not processing the IIF for BODY and the "N" correctly.

 

[lameid]

If that runs, your table structure is different than what you provided.

Please provide some sample data from the the tables involved.

 

[vbahelp07]

GuitarHeader table has:
InvoiceDate
InvoiceNumber
GuitarItem
OptionCombo
basically ...

GuitarDetails table has:
InvoiceNumber
OptionItem

FinishOptions table has:
OptionItems
OptionCategory
basically ...

GuitarOptionDetails is a query with the above 3 tables linked together:
InvoiceDate
InvoiceNumber
GuitarItem
OptionItem
OptionCombo
OptionCategory

 

[lameid]

What is the SQL for the query GuitarOptionDetails?