Get last unique number from a field for each group based on another field and adding

[JohnnyBGood]

Hi,

I want to create an append query to add a record to our Bills of Material.

We have several bills of material that we want to add a part to the bottom of the bill.

So basically I am querying a Requirement table that lists all the parts in a BOM by BOM base id... so I have one field which lists the Base ID and it will be repeated as many times as there are parts in that BOM, then it goes to next Base ID and so on...

Each Part ID has a unique Piece Number field within each Base ID. I want to be able to identify the last Piece Number per Base_ID and add 10 to it, and that is the record I will append with a specific part number. (the same part number is to be added to the bottom of each BOM (Base Id grooup).

Is this possible?

Example of my table

BASE_ID PART_ID PIECE_NO
1234 xx1 10
1234 xx2 20
1234 xx3 30
1234 xx4 40
2345 yy1 10
2345 yy2 20
2345 yy2 30

So I want to identify that 40 is the last Piece_No for Base_Id 1234 and add 10 to it (to give me 50) and for Base_ID 2345, 30 is last Piece_no. I want to add 10 to get 40.... and make those the new Piece_No for new records with a new part zz1 to both these Base_IDs

 

[HiTechCoach]

Try using DMAx()

 

[JohnnyBGood]

Hi, thanks for the reply.

Can you guide me a bit on how I would implement DMAX in my case to add records at bottom of each Base_ID group?

 

[JohnnyBGood]

I think I need a subquery... but having difficulty figuring out how to proceed....

Here is my current Select Query:

SELECT SYSADM_REQUIREMENT.WORKORDER_TYPE, SYSADM_REQUIREMENT.WORKORDER_BASE_ID, SYSADM_REQUIREMENT.PIECE_NO, SYSADM_REQUIREMENT.PART_ID
FROM SYSADM_REQUIREMENT
WHERE SYSADM_REQUIREMENT.WORKORDER_TYPE="M" AND SYSADM_REQUIREMENT.WORKORDER_BASE_ID Like '2BR%';

So I need to select all those records but only where SYSADM_REQUIREMENT.PIECE_NO is maximum for each SYSADM_REQUIREMENT.WORKORDER_BASE_ID.

Then I need to append a record that is a copy of that, but with the next sequential SYSADM_REQUIREMENT.PIECE_NO and a fixed part number: "XXX123"

Not sure if I should first create a table with the above contents, then write a new Append Query based on that.. or if it can be combined into 1 append query.

Hope that it a bit clearer....

 

[HiTechCoach]

I would use a recordset to loop through all the unique Base_Id record. I would use Dmax()+10 to get the next number for the Base_Id. Run an append query to add the record using the new vaukle calculated (Dmax()+10).

 

[JohnnyBGood]

Thanks.

I was able to find some code that helped me to get the MAX record from each BASE_ID, however it worked perfectly in Microsoft Query in Excel with this code, but it doesn't work in Access's query. What do I need to change?

This is the error Access gives me :

syntax error (missing operator) in query expression 'max(PIECE_NO) over (PARTITION by WORKORDER_BASE_ID) max_PIECE_NO'

with this SQL code:

select WORKORDER_BASE_ID, PART_ID, PIECE_NO from (select WORKORDER_BASE_ID, PART_ID, PIECE_NO, max(PIECE_NO)  over (PARTITION by WORKORDER_BASE_ID) max_PIECE_NO from   REQUIREMENT) where PIECE_NO = max_PIECE_NO-10 AND WORKORDER_BASE_ID LIKE '1BR%'

 

[HiTechCoach]

If you think you wan to go the sub query route then check out this:

http://www.allenbrowne.com/subquery-01.html

FWIW:
DMAX() and sub query basically do the same thing. DMAX() is used in VBA. Sub queries are used in SQL. It I was doing this I would use VBA code to generate the SQL.

Boyd Trimmelll aka HiTechCoach
Microsoft MVP - Access Expert

 

[JohnnyBGood]

Okay, I thought of a possible easier workaround (for me anyway)

I created an Excel spreadsheet that lists the rows I want to append into my database and then I linked my Sheet to create an access table.

Now my main database has 7 primary keys and I can't append due to key violations. Ie. no records get added because some of the rows in my Excel sheet contain duplicate values in some columns, but the first column of the first primary key, BASE_ID has all unique records.. other Primary Keys like Lot_ID are almost always set to 0, so that is naturally duplicate....

Is that what is causing the violations or is it something else... or is there a workaround for that?

I have included some pictures of my table definitions that I hope will help.

 

[JohnnyBGood]

Any further advice on my last post from anyone?

 

[HiTechCoach]

Without seeing we really can't give you any specific advice about your database. Only general information.

If this is a onetime task then using Excel might work. If this will be repeats often then a VBA method may be your best solution.

Now my main database has 7 primary keys...

That would indicate 7 tables since a table can only have one primary key.

This leads me to believe that you are having to append to multiple takes. If this is true, my experience tells me that this probably can't be done with just a SQL (query). If there some reason you will not or can not use VBA code?

 

[JohnnyBGood]

Hi,

If you look at Pic1.jpg I attached before, there are 7 fields with a key beside them, that is what I meant by 7 primary keys.

Pics doesn't have any keys, that is my import from Excel that I want to append to the table in Pic1.

Pic3 shows the Excel important in Table View mode.

Field 1 is the one I consider to be the "real primary key" as it is the one that is unique..... The PIECE_NO, TYPE, LOT_ID, SPLIT_ID and so on can be duplicated... but not for the same BASE_ID (Field 1).

I am not sure why I am getting the key violations error and how to work around it.

I am not proficient in Access.. and so I don't want to tackle using VBA at this moment. It is going to be a task that may be done once every year or so.. and maybe not ever again, but would be a good tool to have if I need to do this again in the future.

 

[JohnnyBGood]

So those 7 keys is a composite Primary key made up of 7 columns.

I tried doing an import table from Excel and setting the same 7 columns as a composite primary key... and I still get the key violations error.

 

[HiTechCoach]

So those 7 keys is a composite Primary key made up of 7 columns.

I think you really meant to say: So those 7 fields are a composite Primary key.


I have never had to have a primary key that was more that one field. In 1000's of database it has never been needed. I have yet to see a database that would only work with a composite Primary key.


You composite Primary key. indicates database design issues with normalization. Which is probably why this is so difficult.


Is it possible for you to post a sample database?

 

[JohnnyBGood]

This is our ERP (Enterprise Resource Planning) database. I am not sure how I would post a sample of that and also there are confidentiality issues.

I am not even sure that this Primary key is the issue... all I know is I get the typical key violations error and is states the 6 records have not been added due to key violations. There are only 6 records I am trying to add (to start). I also tried with just 1 record and still no go.

 

[HiTechCoach]

This is our ERP (Enterprise Resource Planning) database. I am not sure how I would post a sample of that and also there are confidentiality issues.

Ah ... OK.

I am not even sure that this Primary key is the issue... all I know is I get the typical key violations error and is states the 6 records have not been added due to key violations. There are only 6 records I am trying to add (to start). I also tried with just 1 record and still no go.

Are there relationships between this table any other table that has Enforce RI enabled?

I would look for a missing related record in another table. One way yo do this is make a query using the table you want to append and add all the other tables to find which one has missing data.

 

[JohnnyBGood]

Hi again,

I think I have finally figured it out.

It was violating some foreign keys. There are a few in this table.

Those foreign keys required columns to be null or existing items in those foreign key tables.... I had 0's in some of them, causing the problem.

Now it appended with no issues.

Thanks once again for your guidance through all this.

 

[HiTechCoach]

You're welcome.

Thanks for the update and your solution.

Good luck with your project.