Query to add record to other table if not yet there

[robs23]

Hi Guys,



I have a form in datasheet view that has allow additions property set to true. Record source of this form is set to select query based on 2 tables, here's query code:

SELECT tbTrucks.truckId, tbTrucks.plateNumbers, tbForwarder.forwarderDataFROM tbForwarder RIGHT JOIN tbTrucks ON tbForwarder.forwarderID = tbTrucks.forwarderId;

Now, when I fill in the last row I want it to behave like this:

1. add new record to tbTrucks with plateNumbers I filled in
2. check whether the forwarderData I filled in matches any existen tbForwarder record. If yes, put existent tbForwarder.forwarderId into tbTrucks.forwarderId. If the forwarder doesn't exist, add new record to tbForwarder and then put this new tbForwarder.forwarderId into tbTrucks.forwarderId.

Currently I end up with new tbForwarder record created even though the forwarder of data I entered already exists. In other words it doesn't check if forwarder already exists in tbForwarder and always creates new record for it. This way I end up with a bunch of exactly the same forwarder's data in separate records (hence treated as separate forwarders later). How can I work this around?

PS. Sorry if I didn't make it clear enough, the issue is quite complex and abstract as for my command of english.

Robert

[orange]

It would be helpful to readers if you showed us your table designs and your relationships window.
If you are getting duplicates, it seems you do not have the appropriate Primary keys, or do not have unique indexes set on these important fields.

Getting tables and relationships set up to meet your business needs is critical.

[robs23]

Honestly speaking it's not much of a design but here it is

[orange]

Ok. Let's step back and describe each table --brief description of what it is.
What is the relationship between a Truck and a Forwarder--that is how are they related in simple plain English?

You have a list of Trucks and some of those Trucks are Forwarders--- that's what I see from the diagram. Is that the intent?
Should your relationship "point" the other way?
Can you post a copy of the database?

[robs23]

The names of tables should more-less reflect relationship - tbTrucks contains all the truck (and their plate numbers) of all the forwarders (middlemen that organize transport for us). The relationship is simple, each forwarder can have many trucks, but single truck can never have more than 1 owner (forwarder). So it is 1(forwarder) to many (trucks) relationship.

EDIT: Please find simple db attached to ilustrate the issue. Just open "frmForwarderPicker" and add new row. E.g. add new truck of plates numbers "33333" and put "Company2" as forwarder - it will put new "Company2" record into tbForwarder even though such company already exists. I want it to figure out there's already such forwarder and to put its key there instead of creating new forwarder/key.

Normally i would do this in VBA with use of buttons but I found additions in new row solution interesting.

[orange]

I think your table structure may be incorrect. I don't understand your JOIN.

If you have Forwarders and Trucks, and all Trucks can be related to a Forwarder(s), you could probably have 2 tables

Forwarder and ForwardersTruck. However, you said Trucks was for all trucks of all forwarders.

So I think a 3 table structure may be more appropriate.

I am attaching 2 jpgs to illustrate(2 table and 3 table). I don't know what attributes you need in the middle/junction table. In fact I'm guessing at your needs since you really haven't described your business -- other than forwarders transport for you.

It is important to get your terminology consistent. Things like Owner/Forwarder is confusing to us who do not know your business nor "trucking/transporting". If you could describe in plain English --a day in the life of our business--, it may help you get more focused responses.

After Posting I saw your database attachment.
I looked at it and am a little confused. You used owner and forwarder some what interchangeably earlier.

The relationship is simple, each forwarder can have many trucks, but single truck can never have more than 1 owner (forwarder). So it is 1(forwarder) to many (trucks) relationship.

It seems that you may want to separate out Owners vs Forwarders--but that isn't clear.

If a Forwarder can have Many Trucks that would be 1 to Many, with the relationships much like that in the 2 table jpg.

The ForwarderID is PK of the Forwarder table and a FK in Truck table. Being a PK the ForwarderID would not be added more than once to the Forwarder table. That is the design of a 1:Many relationship and is enforced by Access. Your join is incorrect.

[robs23]

@orange First of all let me say thanks for effort and willingness to help. I appreciate that a lot.
Forwarder - in my understading - is a company that owns trucks and offers to ship your loading. So fowarder = owner (yes, they're interchangable). Truck is obviously a single truck that is used to carry your loading from point A to B. As it is 1-to-many relationship, I don't think any middle table is needed here. I don't see any additional value provided by such solution. In my solution you can assign as many trucks as you want to single forwarder - you simply have to put forwarderId in tbTrucks as foreign key in the row of desired truck.

Anyway, the main point here is to be able to add record to 2 tables at once or just 1 table if the second has already got appropriate record. Can your solution do that?

Robert

[orange]

I think it would be helpful if you told us more of the details of your business.
So far you have Forwarders and Trucks. As I understand it, you do not want to separate out, nor use, Owners. Owners are Forwarders and the label Forwarder covers it.
But what about the business? What is the database going to support?
Do you need to track loads? Which Truck makes which Delivery? What goes into a Load?
It seems that a list of Forwarders and their Trucks doesn't handle all aspects of the business, but you haven't told us much.

If you add a new Truck to your database, you would have to identify its Tag and the Forwarder. If the Forwarder is new, you must add the Forwarder to the Forwarder table; then add the Truck to the Truck table identifying the Tag and the Forwarder.
That will keep your tables "in sync". If the Forwarder is already in the Forwarder table, it will not add a duplicate record. It will use the ForwarderID and use it when adding the new Truck record to the Truck table.

[robs23]

Could you attach my sample database made over in line with what you described? I can't understand it:/

[orange]

I modified your database sample. I created relationships --see relationships window under database tools.
I created a new form. I added some records to your existing tables.
I created a new form and subform linking them on ForwarderID.

You can add a new forwarder in the new form, then add new trucks for that forwarder.

This is for demo only.

Good luck.