I think we are getting a little off track
tblPartNumber is the table I keep the part number and description only, this
is the only info I need in this database per part.
EXTENTION along with another field called BASENUMBER, is what makes up my
ECO numbers. These are in my tblECOLOG, and there is a bunch of other info,
about the ECO.
I played around and was able to do this manually using my access info and
excel, maybe if I told you what I did, it would help?
First, I ran this query
SELECT [ECO LOG].[BaseNumber] & "-" & Format([ECO LOG].[Extension],"000") AS
[ECO Number], tblAffectedParts.BaseNumber, tblAffectedParts.Extension, [ECO
LOG].DATE_ENTERED, [ECO LOG].DATE_RELEASED,
tblAffectedParts.tblPartNumber_ID, tblAffectedParts.ID1, [ECO LOG].STATUS,
tblAffectedParts.REVISION
FROM [ECO LOG] INNER JOIN tblAffectedParts ON ([ECO LOG].BASENUMBER =
tblAffectedParts.BaseNumber) AND ([ECO LOG].EXTENSION =
tblAffectedParts.Extension)
WHERE (((tblAffectedParts.BaseNumber) Not Like "CACR" Or
(tblAffectedParts.BaseNumber)="CAR") AND (([ECO LOG].DATE_ENTERED) Between
#1/1/2005# And #12/31/2005#));
Then I copied the results into Excel, sorted by the revision and deleted
everything except for the parts that had revision “A†through “Bâ€.
I did this because as I was working I figured out that I didn’t need
revision “C†thru “Z†because I just need to find if it changed within 30
days from the time it was released as revision “Aâ€.
Then I sorted by the part number and went through manually and checked all
the parts that changed from “A†to “B†if it was within 30 days of each
other, then, highlighted them. Then I just went back deleted the ones not
highlighted, and presto I have the number of ECOs that had changed within 30
days of being released.
There has to be a way to do this with Access.
Thanks
KARL DEWEY said:
I do not follow the purpose of some of the fields and how they need to
relate. The way I would do it is like this --
tblPartNumber --
tblPartNumber_ID autonumber
field name Part_Number TEXT
other fields with information on manufacturing, testing, design
tblECOLOG
Field name tblPartNumber_ID type Number
Field name REVISION type Text
Field name DATE_ENTERED type Date/Time
These two table would be related on tblPartNumber_ID in a one-to-many
relationship.
I do not know what the purpose of field EXTENSION and ID1 serves.
Tammy said:
Hi thanks for responding,
sorry about the tblECO LOG that is a typo when I was writing the table is
tblECOLOG
Here are the tables
tblAffectedParts
Field name REVISION type Text
Field name ID1 type Number
Field name tblPartNumber_ID type Number, this looks up the part number in
a table called tblPartNumber, field name Part_Number which is a Text field.
Field name Extension type Number
tblECOLOG
Field name DATE_ENTERED type Date/Time
Field name EXTENSION type Number
Thanks again
:
Post your table structure with field names and datatype.
You have two different name for one table in the SQL --
[tblECO LOG] [tblECOLOG] with space and no space.
:
Hello everyone,
I need some help
I have an engineering database, that keeps track of ECO’s and the revisions
(a letter that tells you that there has been a change to a part) of the parts
that are affected by the ECO.
I have been given the task to provide the number of ECO’s that have occurred
within the first 30 days of the release of the part.
When we release a part number the first release is revision “Aâ€, so what I
need to do is get a query of all parts that have been changed from revision
“A†to any of the other revisions, “Bâ€, “Câ€, “Dâ€, “E†and so on, and only
show the ones that were changed within 30days of revision “Aâ€
So this is what my query looks like right now
SELECT tblAffectedParts.REVISION, tblAffectedParts.ID1,
tblAffectedParts.tblPartNumber_ID, [tblECOLOG].DATE_ENTERED
FROM [tblECO LOG] INNER JOIN tblAffectedParts ON [tblECOLOG].EXTENSION =
tblAffectedParts.Extension;
Please help,
Thanks,
Tammy
