I am building a product database to support a web site, and am having trouble combining fields from two Access queries into a third. The first query contains one record per product while the second query contains multiple records per product. I want the third query to have one record per product, with the information from the second query spread over multiple fields.
The first query, qryProducts, holds specifications for many products. These data are used to populate the web page with information about a product selected by the site visitor. There is one record per product.
The second query, qryClicks, contains data needed to place links on each product page. Fields include Productname, Linktext, URL and Sequence. (The URL’s link to the websites of a product's manufacturer, dealers, etc.) There are a large variety of these links, although usually only 1 – 6 per product.
The two queries are linked via the common Productname field.
I need to provide to my website builder a single Access query, qryProductdata, that contains all data to be placed on a given product’s page. The query should have only one record for a Productname. In this record, the data from the single record in qryProducts will be placed. The multiple records from qryClicks will be spread into multiple fields, e.g. Linktext1, URL1, Linktext2, URL2, up to Linktext20 and URL20. The 1, 2, etc. in the field names refer to the Sequence field in qryClicks. Most of these fields in qryProductdata will be empty, as a given product will have only 1 - 6 links.
To pull the right data from qryClicks into Linktext1, URL1, etc., I have tried placing expressions on the Field line and the Criteria line of qryProductdata’s Design View. The results are either a blank query or a query with multiple lines per product.
I’d be grateful for any help, preferably in terms of the Query Design View in Microsoft Access. I haven’t worked with SQL or VB yet.
The first query, qryProducts, holds specifications for many products. These data are used to populate the web page with information about a product selected by the site visitor. There is one record per product.
The second query, qryClicks, contains data needed to place links on each product page. Fields include Productname, Linktext, URL and Sequence. (The URL’s link to the websites of a product's manufacturer, dealers, etc.) There are a large variety of these links, although usually only 1 – 6 per product.
The two queries are linked via the common Productname field.
I need to provide to my website builder a single Access query, qryProductdata, that contains all data to be placed on a given product’s page. The query should have only one record for a Productname. In this record, the data from the single record in qryProducts will be placed. The multiple records from qryClicks will be spread into multiple fields, e.g. Linktext1, URL1, Linktext2, URL2, up to Linktext20 and URL20. The 1, 2, etc. in the field names refer to the Sequence field in qryClicks. Most of these fields in qryProductdata will be empty, as a given product will have only 1 - 6 links.
To pull the right data from qryClicks into Linktext1, URL1, etc., I have tried placing expressions on the Field line and the Criteria line of qryProductdata’s Design View. The results are either a blank query or a query with multiple lines per product.
I’d be grateful for any help, preferably in terms of the Query Design View in Microsoft Access. I haven’t worked with SQL or VB yet.
AS LinkText1, IIf([qryBoatClicks].[Sequence]="1",[qryBoatClicks].,"") AS URL1
FROM qryBoat...oatCalcs.Boatname;
Thanks for looking at it.