Append Query Multiple Criteria (1 Viewer)

sunnytaru

Member
Local time
Today, 18:08
Joined
Mar 24, 2022
Messages
41
Hello Mentors,

I want to understand the logic of how I can get this working. I have two tables JPPNewProjectTBL and ProjectCostTBL. I need some fields appended in the Project Cost table but there are multiple criteria's. Fields are ProjectID MemberID DocumentTypeID JPPMemberStatus.
Criteria's to append are, the Project ID to only duplicate when either of them are different MemberID, DocumentTypeID, JPPMemberStatus.
JPPMemebrStatus could be either 1 or 2 and I have 13 DoucumentTypeID and 16 MemberID and document Type will always be different if revised in most cases it is 1.

The Project is only once with Status ID 2 and Document Type ID 1 and that is under development stage. Once the Project is Active the Status ID is 1 and if there is any amendment to contract, then Document Type ID will always be a different number.

Just Visual Display

Project IDMember Status MemberID Document Type
2001​
2 (under development ) 1(AECL) 1 (JPP) (Original Contract)
20012 (under development ) 2(BP) 1 (JPP) (Original Contract)
20011 ( Active) 1(AECL) 5 (JPA1) (Revision 1 contract for same project)
20011 ( Active) 2(BP) 5 (JPA1) (Revision 1 contract for same project)
20012 (under development ) 5 (HQ) 5 (JPA1) (Revision 1 contract for same project) (as new member Joining)






1648645396286.png
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:08
Joined
May 7, 2009
Messages
16,826
create a compound index on those 4 fields (without duplicate).
then you can use insert query that does not need any criteria.
(btw, only update query has criteria?

¯\_(ツ)_/¯
 

plog

Banishment Pending
Local time
Today, 17:08
Joined
May 11, 2011
Messages
11,012
You're criteria made no sense to me, so I am just commenting on the bigger issues I was able to see:

1. Action queries (UPDATE, INSERT, DELETE, etc.) are generally hacks around a poor table structure. Why do you need to move data into a table rather than just the primary key of where that data is coming from? Why not just put the ID of JPPNewProjectTBL into the Cost table and that way you have all the data from it you need by reference?

2. Your DocumentType table is not properly normalized. Every discrete piece of data needs to go into its own field. You've crammed at 3 pieces of data into the DocumentType field. Take JPP-R3 for example: JPP is a discrete piece of data and needs its own field, R is a discrete piece of data and needs its own field, 3 is a discrete piece of data and needs its own field. That table needs at least 2 more fields. And that's the only table I caught a glimpse into. I fear you've done this or made other normalizations errors in the tables I can't see.
 

sunnytaru

Member
Local time
Today, 18:08
Joined
Mar 24, 2022
Messages
41
You're criteria made no sense to me, so I am just commenting on the bigger issues I was able to see:

1. Action queries (UPDATE, INSERT, DELETE, etc.) are generally hacks around a poor table structure. Why do you need to move data into a table rather than just the primary key of where that data is coming from? Why not just put the ID of JPPNewProjectTBL into the Cost table and that way you have all the data from it you need by reference?

2. Your DocumentType table is not properly normalized. Every discrete piece of data needs to go into its own field. You've crammed at 3 pieces of data into the DocumentType field. Take JPP-R3 for example: JPP is a discrete piece of data and needs its own field, R is a discrete piece of data and needs its own field, 3 is a discrete piece of data and needs its own field. That table needs at least 2 more fields. And that's the only table I caught a glimpse into. I fear you've done this or made other normalizations errors in the tables I can't see.
Thanks for the response, I am a beginner in access so what you see I do not yet. Both tables are setup in this way as there are stages in the process that I need to follow and I was trying to make it work that way, from what you are saying I need to add 2 more fields Name and Revision# ?
 

sunnytaru

Member
Local time
Today, 18:08
Joined
Mar 24, 2022
Messages
41
create a compound index on those 4 fields (without duplicate).
then you can use insert query that does not need any criteria.
(btw, only update query has criteria?

¯\_(ツ)_/¯
But I will have duplicate ProjectID
 

plog

Banishment Pending
Local time
Today, 17:08
Joined
May 11, 2011
Messages
11,012
Let's take a step back from the database for a minute. Take a paragraph and explain what it is your organization does. No database jargon, just pretend your explaining to elementary kids what it is you do.

Then, take a paragaraph and explain what it is this database will help you with--keep the database jargon to a minimum.

Finally, complete the relationship tool in Access, expand all the tables so we can see the fields, then take a screenshot and post it back here. That way we can make sure your tables are set up correctly.
 

Users who are viewing this thread

Top Bottom