Using a Query to find multiple items

[Key Powell]

I am not too sure how to word this, but:

I am attempting to create a database to track job information and pricing.

I have a query that calculates the price of a fixture based on various
criteria.

I have a table that stores the name of the job (primary key) and multiple
columns that store fixture number and quantity.

I need to use a query to look up the fixture number from the Fixture Price
Query and multiply it by the quantity to give me the total. My problem is
that I need the query to do this multiple times in one row. Here is the
example as best as I can show:

QRY:
Job Name - Fixture #1 - Fixture #1 Price (look up from query) -
Fixture #1 Qty - Fixture #1 Cost - Fixture #2 - Fixture #2 Price
(lookup from qry) - Fixture #2 Cost

I appreciate any help you all my have (if it is even possible)

 

[KARL DEWEY]

You need Fixture #2 in a second row/record.

 

[Chris2]

I am not too sure how to word this, but:

I am attempting to create a database to track job information and pricing.

I have a query that calculates the price of a fixture based on various
criteria.

I have a table that stores the name of the job (primary key) and multiple
columns that store fixture number and quantity.

I need to use a query to look up the fixture number from the Fixture Price
Query and multiply it by the quantity to give me the total. My problem is
that I need the query to do this multiple times in one row. Here is the
example as best as I can show:

QRY:
Job Name - Fixture #1 - Fixture #1 Price (look up from query) -
Fixture #1 Qty - Fixture #1 Cost - Fixture #2 - Fixture #2 Price
(lookup from qry) - Fixture #2 Cost

I appreciate any help you all my have (if it is even possible)

Key Powell,

I do not know what your table structures are, so I will use some examples as guesses.


Tables:

Create a blank MS Access database. You can copy and paste these DDL SQL queries each into
an MS Access Query, executing each one in order to create the tables and the
relationships.


CREATE TABLE Fixtures
(FixtureID AUTOINCREMENT
,FixtureName TEXT(255) NOT NULL
,CONSTRAINT pk_Fixtures
PRIMARY KEY (FixtureID)
)

CREATE TABLE Prices
(PriceID AUTOINCREMENT
,FixtureID INTEGER NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME
,Price CURRENCY
,CONSTRAINT pk_Prices
PRIMARY KEY (PriceID
,FixtureID
,StartDate)
,CONSTRAINT fk_Prices_Fixtures
FOREIGN KEY (FixtureID)
REFERENCES Fixtures (FixtureID)
)

CREATE TABLE Jobs
(JobID AUTOINCREMENT
,JobName TEXT(255) NOT NULL
,CONSTRAINT pk_Jobs
PRIMARY KEY (JobID)
)


CREATE TABLE JobInformation
(JobInformationID AUTOINCREMENT
,JobID INTEGER NOT NULL
,FixtureID INTEGER NOT NULL
,Quantity INTEGER NOT NULL
,CONSTRAINT pk_JobInformation
PRIMARY KEY (JobInformationID)
,CONSTRAINT pk_JobInformation_Jobs
FOREIGN KEY (JobID)
REFERENCES Jobs (JobID)
,CONSTRAINT fk_Jobs_Fixtures
FOREIGN KEY (FixtureID)
REFERENCES Fixtures (FixtureID)
)


Sample Data:

Fixtures
1, Item One
2, Item Two
3, Item Three


Prices
1, 1, 06/01/2007, 06/30/2007, 10.00
2, 1, 07/01/2007, 07/31/2007, 10.50
3, 2, 06/20/2007, 06/30/2007, 20.00
4, 3, 01/01/2007, 12/31/2007, 30.00

Jobs
1, Thing One
2, Thing Two
3, Thing Three

JobInformation
1, 1, 1, 20
2, 2, 1, 10
3, 2, 2, 15
4, 3, 1, 1
5, 3, 2, 1
6, 3, 3, 1


TRANSFORM SUM(P1.Price * JI1.Quantity)
SELECT J1.JobName
FROM ((JobInformation As JI1
INNER JOIN
Fixtures AS F1
ON JI1.FixtureID = F1.FixtureID)
INNER JOIN
Prices AS P1
ON F1.FixtureID = P1.FixtureID)
INNER JOIN
Jobs AS J1
ON JI1.JobID = J1.JobID
WHERE Date()
BETWEEN P1.StartDate and P1.EndDate
GROUP BY J1.JobName
PIVOT F1.FixtureName


Results:

JobName, Item One Item Three Item Two
Thing One, $200.00
Thing Three, $10.00, $30.00, $20.00
Thing Two, $100.00, $300.00


Hopefully, you were looking for something like that.

Go ahead and adapt that to your needs based on your table structures and data.


Sincerely,

Chris O.