tricky query problem updating junction table

[inkman]

I have recently split a "Customer table" into "Customertble", "Usernametble" and a "Junction table" (linking them).This all works very well.However, I have a macro that imports data from the web as a csv file, and puts the data into a table "new user".The macro then activates the update query to drop that data(appropriate fields)into the "customer table".This worked perfectly when all the data went into just the "Customer table". I currently (with the new table configuration)can get the data to be added to both the "Customertble" and the "Usernametble", but cannot get the "junction table" to be updated. Any suggestions would be most welcome?

 

[fneily]

See:

http://msdn.microsoft.com/en-us/library/aa198446.aspx

The reason for a many-to-one-to-many explanation. Basically, it seems, you're trying to update foreign keys that aren't yet primary keys.

 

[inkman]

fneily, thanks for your reply. Yes I think that is the problem. I cannot think of a way around this. Basically I am just trying to add a new web customer to the database.The data is typically manually entered via a customer form and username sub form (hence the junction table link).If I manually put this information into these tables (via the customer form/ username subform) the junction table is updated as required (no problem).Is there a way to get around the above issue? I am not very competent in access. Thx
Ian

 

[fneily]

Can you show your table structures of Customertble and Usernametble? Are you saying that a customer can have many usernames AND a username can belong to many customers? That doesn't make sense to me. So I now don't understand the junction table.
Also, what does the layout of the csv file look like?

 

[inkman]

fneily, yes customer can have several users and users can be customers in their own right as well as an employee of an existing customer (also the users do move from customer to customer).We have some users that appear in three different customers.

Customertble:
CustomerID
Customername
Pword
UserID
addressID

Junction Table:
CustomerID
UID


Usernametble
UsernameID
Firstname
Lastname
email
phone

They are linked one to many to the junction table

in the Customer form they are linked as above plus an address table (the addresstble is linked to the customer tble via addressID).

The CSV file is being imported correctly to a "newuser table". The update query takes the info from the "newusertable" and puts it into the above tables (except the junction table).

I hope this helps or is enough info.

Regards
Ian

 

[fneily]

Much clearer.
A couple of things: Tables should have primary keys. So your junction table should have a primary key. Thus
tblCustUser (made this up)
CUID Primary Key
CustomerID
UserNameID

Note that I'm using UserNameID because that's the way it's spelled in Usernametble. Try and keep the same name for the same fields. Access can then connect them automatically.

Also, as you said, a user can now be connected to three customers.

You can fill the CustomerTble and UserNameTble with your macro. Does the csv file all ready match up the customers/users? If not, I'd then create a form based on tblCustUser where CUID would be an autonumber, and then have comboboxes displaying the CustomerID and UserNameID with the comboboxes control sources to CustomerID and UserNameID in tblCustUser.