right i've been trying to think how to do this and i can't work it out... really shouldn't have got myself into the situation in the first place but i'm hoping its easier than i think so here goes

i have two tables TBL_Batches and TBL_Jobs, there are batches of jobs, many jobs to one batch so the tables are linked in a one to many relationship

there is a field in TBL_Jobs called job type which can be typeA or typeB and all the batches contain a mix of typeA and typeB jobs

the end result which i need to achieve is to have two entries in TBL_Batches for every one i have at the moment, one of them containing only typeA and the other only typeB... so where i have batch1 i should have batch1a and batch1b remove batch1 and all the foreign keys in TBL_Jobs changed so they link to the appropriate new batch in TBL_Batches

any ideas?

Why? A simple parameter query will pull out type a or type b jobs.

i know and i still want to be able to load mixed batches (which i will still be able to do and still will do after splitting these)

the problem is that the batches relate to invoices and i want to load the data about the invoices and i have situations where a batch is split across two invoices because there was an invoice for the typeA and another for the typeB

Are you saying that invoices only ever include one type of job?

no sometimes they do and sometimes they don't (usually they do though)

also each invoice comes under a contract and the batches table has a contracts field

sometimes as i have the data at present a batch falls across two contracts with the typeA under one contract and the typeB under another

Why do you need to split your tables. You can write simple queries to give you views of your data as if the tables were split when you need it. A query gives you a data set containing the records you actually need in a particular instance.

Nothing posted here so far gives a good reason for splitting the tables. It won't acheive anything that you can't do with queries.

I agree with Rabbie. I see a possible need for an invoice table linked to the jobs table, but you haven't explained the business process in enough detail to be sure of that.

ok possibly its my table design which is at fault

contracts are linked to batches, one contract to many batches i don't currently have a separate invoices table (i should have), currently got fields relating to invoices in the batches table

i can see that if both contracts and invoices were linked to jobs then i wouldn't need to split the tables, or alternativly if invoices were linked to contracts and contracts were linked to jobs which i think makes more sense than linking both to jobs

your right it is just the way my relationships are set up at the moment which means i need to split the tables but the way they are set up makes sense to me (apart from not having an invoices table but i'm trying to remidy that at present)

the batches play an imprtant role in the way we do business with our partners we don't ever do anything on a job by job basis because we deal with too much work communication with our partners works on the basis of excel files representing batches

Ultimately, it's your database. All we can do is offer advice and it's up to you what you do with the advice. Also bear in mind that you have a much greater knowledge about how your business works.

I'm not a trained programmer and I don't get too hung up on following the 'rules', but normalisation is key to proper design. This is about managing the entities in your process. An entity is an individual component and I would expect to see one record for each entity. I interpreted your information as follows:
1) A batch is one entity. It can have multiple jobs, but a batch is a batch so one record
2) A job is an entity, so one record per job. These are linked in a M to 1 with batches
3) An invoice only relates to one batch, but a batch can have more than one invoice. However if there are 2 invoices they relate to type a and b jobs. So linking this to a batch does not give you the whole story, hence my suggestion that they are linked to jobs
4) If the only purpose for splitting the batches is to cope with the two invoice cases, then I don't see how this is preferable to linking to the jobs, since the jobs table identifies the batch anyway. If there are other reasons for splitting the batches, you haven't explained that

Does this help?

yeah that is all right, thinking about it there a re a couple of other things i haven't explained though sorry

firstly an invoice only relates to one batch but a batch can relate to more than one invoice as you said, however the typeA typeB case is not the only situation under which there may be more than one invoice for a batch

part of the purpose of the databse is checking the validity of the batches (mainly making sure they have claimed for the right amount of money and that they don't duplicate jobs) if there is something wrong with a batch then credit notes get issued which are negative invoices and relate to the batch which had the errrors in it

i don't suppose that makes any difference its just another reason for having an invoices table

the other thing is that i want to represent the batches the way we actually recieved them, which is split into typeA and typeB, the reaon they are the way they are is they are back data loaded from an old excel file system where all records for a given partner were just chucked into one massive excel file one line per record