Importing data then assigning the new records to a project.

[Homegrownandy]

Just after some advice for the best way to do this,



I can import a list of x amount of sites. (Sites are locations we are to carry out work)

Then in my job table I need the sites I added to be put there and then assigned to a project.

example: Site_ID > 350 are all added to jobs and Project_ID in the jobs table = 2

How would I define which project they go into? I can move the site ID's fine, then change each project number manually.

But is there a way to define what I want that field to be at query level?

[ranman256]

sounds like you need to run an update query. Import the new Sites, use this list to join to the tProject table on [site_id]

update [projectID] from tProjects where tProject.[site_id] = tSite.[site_id]

[Homegrownandy]

Thanks for the reply. Ill look into this now.

[orange]

Andy,

You should start with a clear statement of what you are trying to do in plain English. A description of the business, if you will, at a fairly high level of abstraction.
You mention Sites, Jobs and Projects -and these may be extremely clear in your head, but not to readers.
You define Site

Sites are locations we are to carry out work

What exactly is a Job? A Project? in your business.

Build a model and set up relationships based on your business rules.

For example: (I'm not sure from your post if Job has Projects or Projects have many Jobs)

A Project may consist of 1 or more Jobs
A Project occurs at 1 or More Sites....

There is an old saying --if you can't describe it, nobody can build it.

Take time to identify exactly what you are trying to do. OR, said differently -take time to describe the business and the rule you database is intended to support.

[Homegrownandy]

I do see your point orange, but I don't want anyone to build it. Its not a more of a theory question, any additional information I provided was just to give you an idea what I'm doing.

However I do agree the question isn't clear.


basically I'm running a saved import procedure that will add sites. These sites are then made into jobs. (they are only separated for the rare occasion the site is one we have been to before.) Each job is part of a project.

But the question is, yes its easy to import sites.. maybe 20-30 at a time.

Each time I add the sites ALL of the ones I'm adding need a project.....the same project... (I only upload ones for the same project.)

The append query I made will add the sites to the job table.. I'm just wondering if I can define the project in the append query. Or update as suggested by Ranman.

when I say can I define the project in the append query I mean.. can I make the project_ID field be 2 on these new records by default... or 3/4 (any number).

I could change the default value of the project on jobs table be what I want. Then change it back. It just seems like a lot of room for error, So If I can define it on the query I would like that.

Andy .

[Homegrownandy]

Then in my job table I need the sites I added to be put there and then assigned to a project. 

 example: Site_ID > 350 are all added to jobs and Project_ID in the jobs table = 2

this explains it totally, if you have an interest in what I'm doing that's great, I can talk all day about it. I just need a field to be a value I define. there's no need for other information or an understanding of what a project is. I'm not being funny with you, I appreciate all help.