"At most one record can be returned by this subquery."

[Greyowlsl]

Hi i have a query which has the above (thread name) problem coming up when i try to run the query.

The query should display all the products ("product id as on product") where the "product id" = "LS1212" and the "c customer specific" does not = 0.

Attached is an image of the query design and a sample of the 1500 table data.

Thanks for your time.

Kind Regards,
Leon

 

[boblarson]

A subquery by default can only return one record as it is being used to filter a field essentially with a value. So you can have a query pull what you want but you can't use a subquery in your query to pull more than one record for that field. It can return a DIFFERENT single record for each record in your query but if the subquery itself when run returns more than one record it can't be used as the subquery in a field.

I hope that made sense.

Also, having that third table in your query and not linked is going to create a Cartesian Product, just in case you didn't know.

 

[boblarson]

Oh, and having a table with a field for each part is ludicrous (sorry, but I have to state it). That is horrible.

 

[Greyowlsl]

Thanks for your reply Bob.

I think I've figured it out now

As to you second post, the list in the parts table you saw are the products, the parts are the records and the amount that the part is used is listed under each product.

 

[boblarson]

As to you second post, the list in the parts table you saw are the products,

Still not good.

 

[Greyowlsl]

Could you provide a solution as to how to do this better than please?

There is a parts table and a product table
There are around 500 different parts, and around 50 different products.
I need to know how much of each part is used in each product.
Whats the best way to do this?

 

[boblarson]

This is an example of a model for parts that go into products:
http://www.databaseanswers.org/data_models/bom/index.htm

See if that generates any ideas by studying it and then post back.

 

[Greyowlsl]

Ok, but my antivirus software (Avira Pro) has that site listed as a reported attack page ("Part of this site was listed for suspicious activity 2 time(s) over the past 90 days."), not sure if should visit it, but I'm guessing since you work at a large bank you would have far better antivirus protection and would know if its safe right?

For the moment the current database works, and we are doing a full restructuring of our business and computer systems in 6-8 months time, i will take a look at it then.

 

[boblarson]

It was fine for me but just in case for you - here's the image from there:

 

[Greyowlsl]

This helps, thanks Bob

 

[John Big Booty]

I'm getting a similar warning to Greyowlsl.

 

[vbaInet]

I get the same error too...

 

[boblarson]

Strange. I accessed it from work and have had no problem from home either. We have Symantic AV here at work and I use AVG Free at home. Could it be another AV program has it tagged incorrectly, or maybe there was something in the past which caused it to be flagged for them. Weird.

 

[vbaInet]

I remember trying it from home where I run McAfee on one of my laptops and I had the same problem. This is quite recent.