WebCOBRA.com Community Message Boards

kotradov
Posts: 45
Since: 08/25/2008

Hello,
I am looking to get the following information from the MSAccess Relational API:
Employer Group number
Member number
Member name
Bill Due Date
Check number
Check amount
Deposit date

Can anyone give me the information on where this information is in the tables and what the relationship is between the tables?

Thanks

jwolgamott
Posts: 257
Since: 09/26/2005

Here's were you can find the information


Employer Group number - depends on where you add it, most likely the Company Code in Companies.Code
Member number - Likely the QB ID, although SSN or QB Group ID works. Check for QB.QBID, QB.SSN, or QBGroups.NickName
Member name - Likely QB.FirstName and QB.LastName
Bill Due Date - QBGroupInformationCache.PremiumDueD ate
Check number - AccountingPayment.TransactionIdent
Check amount - AccountingPayment.AmountPayed
Deposit date - AccountingPayment.CheckDate or AccountingPayment.DateEntered

The relationship is
Companies > QBGroups > QB

Then, QBGroups have cached information (calculated data) and payments, QBGroupInformationCache AccountingPayment

I hope that answers your question; if not, please let me what you mean by "member name" so we can locate the data for you.

kotradov
Posts: 45
Since: 08/25/2008

I guess first to be clear, I want to explain that we are looking for this information based on monthly payments each individual will be making.

Secondly, can you explain in more detail the relationship that exists that links the QB and the payyments in the Accounting Payment table?

Thanks

jwolgamott
Posts: 257
Since: 09/26/2005

Sure -- QB Groups (families) make payments, and those payments are stored in the AccountingPayment table.

You'll link QBGroups.QBGroupID = AccountingPayment.DisconnectedQBGro upID

The best way to find the amount that the QB Group will be asked to pay each month is by looking at the QBGroupInformationCache table. The TotalMonthlyPremium + TotalMonthlyAdminFee are important, and the TotalMonthlyDue will be the amount they'll be asked to pay.

Note: I keep saying "asked" to pay because the payments rarely are entered on a monthly schedule and do not always match the amount asked. That's why mapping a payment to a monthly invoice is hard (although that information is in the AccountingRegister table)

Let me know if you need any more info or the theory behind the tables!

jsfrench
Posts: 7
Since: 10/23/2009

Can you help me understand how to link between the BankAccounts table and the AccountingRegister? What we are trying to do is to find out the AccountNumber and the RoutingNumber for QBs making ACH payments. Also, the AccountNumber & RoutingNumber appear to be encrypted - please let us know how to decrypt.

Thanks.

sszelei
Posts: 53
Since: 09/02/2009

James,

You can link the tables using the ModuleIdent in BankAccount where ModuleTypeID = 11 and joining that on the CustomerID in Accounting Register.

For security purposes we cannot release information on encryption methods.

Regards,

Steven