Need help with initial design

[Guest]

I am building a new part of my database to show customers/customers
equipment/work done on equipment

Customers - is easy, its the other two parts, I need the equipment to show
the last time the equipment was sent back but to collect the info from the
the last jobsheet to that customer heres a rough idea of what I have
(ignoring customers table for now as its straight forward)

Equipment - EquipID, CustID, EquipDetails, LastDate

Job - JobID, EquipID, Jobdetails, DateIn, DateOut

Relationship - (cust) 1tomany (Equipment) 1tomany (Job)

Thats how I would do my initial design but I know i will struggle to have
the LastDate to pick up the DateOut.

Am I complicating matters more than I need to/is there a simpler way to do
this

 

[Jason Lepack]

I am building a new part of my database to show customers/customers
equipment/work done on equipment

Customers - is easy, its the other two parts, I need the equipment to show
the last time the equipment was sent back but to collect the info from the
the last jobsheet to that customer heres a rough idea of what I have
(ignoring customers table for now as its straight forward)

Equipment - EquipID, CustID, EquipDetails, LastDate

Job - JobID, EquipID, Jobdetails, DateIn, DateOut

Relationship - (cust) 1tomany (Equipment) 1tomany (Job)

Thats how I would do my initial design but I know i will struggle to have
the LastDate to pick up the DateOut.

Am I complicating matters more than I need to/is there a simpler way to do
this

What is the purpose of last date? You could easily use a query at any
point to look that up, therefore I wouldn't store it in a table.

The logic of the query to find the last date would be to find the
maximum dateIn for a given EquipID (if it's still in then it can't be
coming in again) and get the details for the JobID that matches that
date.

Cheers,
Jason Lepack

 

[Guest]

The purpose of the lastdate is so I can recall a piece of equipment after a
specific time (lets say one year), but if a piece of equipment has come in
twice within a year this would make 2 jobs giving 2 final dates - therefore I
want my table to reference one piece of kit and pick up the very last job
using the lastdate, ignoring previous work without losing the history

 

[Jason Lepack]

Here's a query that will allow you to calculate the last job an item
came in on. Once you load that up in the query designer (using SQL
View) then switch to Design Mode it will make much more sense.

SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);

I created a database using your structure just without the LastDate
field. I used this query instead.

Cheers,
Jason Lepack

 

[Guest]

I finally got around to using this but I am getting errors

I copied your text, created a new query, switched to sql and pasted then
switched back to design.

I have then two tables Job and B, but B is not one of my tables!
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Tables are
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip

I also enforced referencial integrity on the relationships

 

[Jason Lepack]

Ah ok,

I think I see the problem... there is no StartDate it's supposed to be
DateIn

Try this:

SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(DateIn) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.DateIn) AND (Job.EquipID = B.EquipID);


B is just an alias for the SubSelect:
SELECT Max(DateIn) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B


Ignore my other post in your repost topic. We'll work from here.