How to add a new item from one table to all customer table?

[JennyL]

Hi

I am trying to add a new item from one table (Tbl_FeeTable) to all customers fee schedule table. I have a table with a master table of fee codes that we use. Each customer gets a fee schedule created in the beginning and the vba code takes all fees (appx 300+) and copy over to the Tbl_CustomerFeeSchedule table. What i am trying to do now is in the future, as we roll out new products, i want to be able for users to add a new fee in the Tbl_feetable and run a query or vba code to tell Access to add that particular fee from the Tbl_feetable and add to all customers' fee schedules. It will help so that we don't have to manually add that one fee code to all 1000+ customers' fee schedules. I have tried update query and append query, but no success. I think ideally I want to create a form, where user can enter or pick the fee code, and then hit "apply to all existing active fee schedules" button. Any recommendation on how I can accomplish this task?


Tbl_FeeTable
FeeCodeID
FeeDescription
StdAmt

Tbl_CustomerFeeSchedule
FeeScheduleID
FeeCodeID
CustomerDescription
OverrideAmt

Thank you in advance for your help!!

[ssanfu]

Not sure I understand your design.

Is there a customer table? I do not see a link to customers in the table "Tbl_CustomerFeeSchedule".

I do see

Tbl_FeeTable
FeeCodeID_PK
FeeDescription
StdAmt


Tbl_CustomerFeeSchedule
FeeScheduleID_PK
FeeCodeID_FK
CustomerDescription
OverrideAmt



Would you post an image of the relationship window or your dB?

[June7]

Right, where is Tbl_Customers and foreign key field in Tbl_CustomerFeeSchedule? You show CustomerDescription - should that be CustomerID_FK?

CurrentDb.Execute "INSERT INTO Tbl_CustomerFeeSchedule(FeeCodeID_FK, CustomerID_FK) SELECT " & Me.tbxFeeID & ", CustomerID FROM Tbl_Customers"

[JennyL]

hi

Here is my table relationships screen.

[ssanfu]

Hummmmmmm..... I don't see a table "Tbl_CustomerFeeSchedule" or "Tbl_Customers" in your relationships screen image.

Note: Shouldn't use "Create" or "Level" as object names as they are reserved words.


I made a couple of changes to be able to see relationships easier (for me).

[JennyL]

thanks for the suggestions - i will rename the create and level fields with different names.

I forgot that I renamed some of the tables haha....Tbl_Parents used to be the customer table, and Tbl_FeeSchedules is used to be the customer fee schedule table.

For the Tbl_CIN, that's additional (sub-customers) related to that fee schedule. Tbl_Parents is the group company name, and within that group, there could be multiple companies so I want to give sales officers a way to reference the sub-companies to the fee schedule for research purposes, in case they are too lazy to look up by the group company name. Not sure if that's the best way to go about creating the tables in database, but so far, that seems to be working.

What i am trying to achieve is to create a form for user to enter the fee code, and then hit "apply this new fee code to all existing active customer fee schedules" on the global level so that we don't have to go to each fee schedule and manually add that one fee. In the beginning when a user creates the company name, and they click on "create" which i will rename it to "generate" box, it will trigger the access to copy all 300+ fee codes in that table at that time and paste to the Tbl_FeeScheduleItems, but i don't know how to trigger to add a new fee code to all existing fee schedules.

Thanks for your help!

[June7]

Have you considered method suggested in post 3?

Once the query is figured out, the real trick is figuring out what event to put code into. Perhaps a button Click.

[ssanfu]

I this point I must confess I am lost.


For the Tbl_CIN, there must be other fields not being displayed. I still makes no sense to have a table with a PK field and a FK field. NO other info is to be n the table.


If every customer/Parent gets every one of the 300+ fees, why not have one table that can be accessed by every customer.parent??

Looking at the relationship image, I can't understand where you are headed (but that's not unusual )



Good luck with your project......

[JennyL]

You are right Steve - in the perfect world it should only be one fee schedule table with multiple customers linked to that fee schedule (parent and sub-companies/customers). However, with my case, there is a chance that each customer could have different version of fee schedules with different prices.

Thank you June7!! Your code worked. Is there anyway I can add a "lookup" to vba below where access will lookup the value in the txtFeeCodeID field to confirm this fee code actually exist in the Tbl_FeeTable before adding it to the table?




Private Sub CmdAddToAll_Click()
On Error GoTo Err_CmdAddToAll_Click
If MsgBox("Are you sure you want to add FeeCodeID " & txtFeeCodeID & " to all existing fee schedules?", _
vbYesNo + vbQuestion, "Confirmation") = vbYes Then

CurrentDb.Execute "INSERT INTO Tbl_FeeScheduleItems (FeeCodeID, FeeScheduleID)SELECT " & Me.txtFeeCodeID & ", FeeScheduleID FROM Tbl_FeeSchedule"
MsgBox "Done"
Else
Me.Undo
End If
Exit_CmdAddToAll_Click:
Exit Sub

Err_CmdAddToAll_Click:
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
Resume Exit_CmdAddToAll_Click
End Sub

[June7]

Sure, use DLookup().

But why let user input an invalid code ID to begin with. Have them select from a combobox with LimitToList set to Yes.

[JennyL]

Thanks - just changed to combo box and it worked!

This brought me to my next problem (which I just noticed), if I repeat the on-click command, it will keep adding that feecodeid since it's not a primary key in the Tbl_FeeScheduleItems.

So when I am doing DLookUp, it has to be looking at both fields (feecodeId and FeeScheduleID) to make sure the record does not exist. Is it possible? I have seen Dlookup with one field lookup, but not two field lookup.

Or maybe I am not setting up the table join correctly between Tbl_FeeTable and Tbl_FeeScheduleItems. I like how it is currently setup because whenever I need to update the Tbl_feeTable such as descriptions and stdamt, it will automatically reflect that in the Tbl_FeeScheduleItems table. How can I keep that relationship link, and still make FeeCodeID a primary key in the Tbl_feeScheduleItems so that it doesn't cause duplicate entries?

Sorry for so many questions and thank you so much for your help!

[June7]

Wait, I thought you had to make sure the code was already in Tbl_FeeCodes?

The search can include as many criteria as you want. Also, the two fields can be set as a compound index and duplicate pairs will be rejected by the INSERT.

[Minty]

Just to throw a slight suggestion spanner in the works here.
We had a price list per customer. Not 300 items but about 60 , and it was a nightmare to maintain. (2000 customers)

So we had a redesign, we had a default price table, and then only stored the exceptions per customer, so if they had 3 different priced items out of the 60 we only stored those 3, and they got the default values for everything else. It took a bit of managing in forms and reports, but the net effect was much simpler maintenance of the entire process.
I could easily display each customers pricing and highlight which pricing wasn't the default.

[JennyL]

June7 - do you have any examples on how I can setup the compound index?

Minty - do you have any examples on your database for the "exception pricing" table? We most likely will have about 1000+ customers so meaning about 1000 fee schedules which each schedule will have about 300+ price items. This means it could potentially be 300,000 - 400,000 records! I am starting to worry that this design might become a problem similar to yours.

Greatly appreciated!!!

[June7]

Bing: Access compound index

http://www.geeksengine.com/article/c...ex-access.html