Automate mutiple append queries - use the Autonumber primary key from 1st query as input in 2nd (2 Viewers)

[jemma86]

Hi,

I have a database where I want to append data from a SharePoint list into my table. (done)
My problem is that once that is done, I want to run a second append query that inputs the AutoNumber Primary Key field created by the first query, and for all this to run automatically when the database is opened.

I am able to do the first query and the automation of running it when the DB is opened, but I am totally stuck on how to get the AutoNumber info - there may be multiple records to append.

Have just thought as I was typing this, of making a temp yes/no field in the first table, that the initial append query checks, then the second query could run if temp yes/no field is checked, then I would need a 3rd update query to uncheck the temp field - this seems pretty messy. Does anyone have a proper/better solution to this?

Thanks

 

[tvanstiphout]

Can you explain the real-world scenario, so our discussion doesn't have to be so abstract?
You're copying SP data into what kind of table? And then copy the Autonumbers (I presume you mean "of the new rows") to what kind of other table?
It is rare to have a table with a single required field (the FK, presumably).

 

[jemma86]

We are starting a new project in a few months where people are going to be referred, they will only be doing one particular course, so I am looking to see if I can automate it, so when the referral form is completed they are automatically added to the contacts table and enrolled onto the course.

We have MS form, power automate into SP list, append query into "Contacts" table. Fields: PK(AutoNumber), name, address, email etc.
Second table is an enrolment table, Fields: PK(AutoNumber), FK (PK from "Contacts"), course, start date etc.

 

[Pat Hartman]

I'm still confused regarding the source of the data. Does the Sharepoint list have the autonumber? If so, you can copy the autonumber in an append query as you append the data into your ACE table. As long as the autonumber from Sharepoint does not duplicate any value that already exists in the ACE table, it will be appended without a problem. When you do this append, how are you selecting which records to get from the SP list? Are you appending all rows or just some?

So, it really doesn't matter whether you are copying the existing PKs from SP or generating new ones. When you do the append, you should add a "batch number" or something to identify the group once it has been added to your ACE table. I do this for all external batch appends. I add a row to the batch table with the source, file name, date. Then I take this BatchID and include it in the append as I copy the data from the external source to the app's table.

As long as you can identify the group of records you just added, you use a second append query to select the new people using the "batch number" and then using other information you gather from the form such as the class they will be enrolled in based on start date or something.

 

[gemma-the-husky]

You need to think carefully about your process and your data tables. You can append autonumbers from one table to just long numbers in another table, which may be sufficient. If you append the autonumbers to an autonumber field, you may get problems if you also create new autonumbers in other ways.

You may not be able to append an autonumber to a SQL server autonumber field though.

I think we are trying to understand how you manage the numbers you are manipulating.

 

[Pat Hartman]

You may not be able to append an autonumber to a SQL server autonumber field though.

You can but the rule is the same - only using an append query.

I think the crux of this problem is identifying the set of data just imported which is why I suggested generating a "batch" number.

 

[gemma-the-husky]

You can but the rule is the same - only using an append query.

I think the crux of this problem is identifying the set of data just imported which is why I suggested generating a "batch" number.

Yes. Of course a batch ref is a good idea. if you still have the external data set, you could match the external data to the imported data as well.

 

[Pat Hartman]

if you still have the external data set, you could match the external data to the imported data as well.

Assuming each row has a unique identifier.