Question Data Collection Replies in Access 2007? Or is my layout wrong?

Thanks for taking a look! I really appreciate you pro's out there helping a newbie out! I hope someday I can do the same, and return the favor!

THE GOAL - TO RECEIVE ORDERS VIA DATA COLLECTION REPLIES THUS ELIMINATING OUR DATA ENTRY MAN HOURS.

THE PROBLEM - MULTIPLE TABLES MAKE UP AN ORDER IN THE DATABASE. * Don't stop reading here, I already know I could use a query, IF I ONLY HAD ONE DEFENDANT PER REQUEST. Please read further.

BACKGROUND - My database collects and reports data on individuals and small companies for the purpose of satisfying collection accounts, reporting criminal history, etc, etc. My customers have been sending word documents attached to email's (Outlook 2007) in order to send us an order request. for the last year or so. Then two users would manually enter the order information in to the database. Our work load has tripled in recent months and it's time for a better system that avoids as much data entry as possible.

NOTE - A (customer) request will always include the basic customer data and at least one Defendant. Sometimes there is only one defendant, other times there may be 2, 3, or even 4 defendants.(the max we allow) Also, often times our customers request a report on only one of the 3 or 4 defendants. * This is because we bill individually for each defendant we research. Also, customers will often specify in what order to conduct research.

For Example, a customer may request a verified current address on ANY ONE of the FOUR DEFENDANTS, but if we can't find one, then the customer wants a bank search instead. *


* TABLES*
Defendant_tbl -
DefendantID (auto), FName, MName, LName, SSN, DOB, AliasName, Address, City, State, PostalCode, etc, etc.
Customer_tbl -
CustomerID(auto), * Various customer data....
Case_tbl -
CaseID(auto), CustomerID(number), etc......
CaseDetail_tbl
CaseDetailID(auto), CaseID(number), DefendantID(number)

* FORMS *
Case_frm -
Detail section conains the selected CustomerID(combo box) and other various order realted information.
CaseDetails_sbf - SubForm
CaseDetailID(auto) - Hidden
CaseID - populates with the vale of the CaseID field in the detail section
DefendantID - combo box which looks up the Defendant_tbl. If the person is not in the list, a form opens to create a new entry which also runs a query which checks for duplicates based on LastName, SSN, and known addresses (in the system)

So, being the layout I have now....How in the world can I get Access Data Collection Replies to work for me?

* Please keep in mind, I need ONE FORM which allows a customer to enter their PO# etc, but also allows them to enter up to Four Defendants. I also need to scan these defendants for duplicates BEFORE they are entered into the system.

Thanks EVERYONE who looks!

so... what's not working? how far have you come already (sounds like you've pretty much already got the thing working?)

Thanks wiklendt!

I'm trying to find a way to allow my customers to send me their orders via email where I can use Data Collection Replies.

The biggest issue is how do I let them make multiple defendant entries on one order form plus how do I check that data for duplicates before allow access to enter it into the table.

Access 2007 can create an email based on a table within your database.

It is a very simple wizard but maybe more difficult for you since you have multiple tables - something for you to look into though.

cbrace09 said:

Thanks wiklendt!

I'm trying to find a way to allow my customers to send me their orders via email where I can use Data Collection Replies.

The biggest issue is how do I let them make multiple defendant entries on one order form plus how do I check that data for duplicates before allow access to enter it into the table.

Click to expand...

sorry, i meant - is there and error coming up for you somewhere in your current database file when you try to do something.

...or have you not started creating the database yet and just want our opinion?

Please read through the posting carefully and you will understand what I have already done and what I want to do.

Thanks for looking!

cbrace09 said:

Please read through the posting carefully and you will understand what I have already done and what I want to do.

Thanks for looking!

Click to expand...

i have read your posts dozens of times. i am no clearer as where you are up to or what you are SPECIFICALLY having a problem with.

i understand what you WANT to do, but i have NO IDEA based on YOUR previous POSTS what you have already done and what is yet to be done.

from the best i can gather from your first post, you have everything working perfectly and have no need for further assistance. yet i know this cannot be true because you would not have otherwise posted.

in one paragraph, you say you have a query that searches for duplicates, in another paragraph further down you say that's a feature you need - i don't actually know where abouts in this development you are at.... is it finding duplicates but still allowing you to save? is it not checking for duplicates? do you even have that query built?

if YOU read your OWN post VERY carefully, you'll realise that there is NOT EVEN ONE question NOR question MARK in it.

thanks for looking!

From what I've read of the original post, you are not going to be able to use the Data Collection method of Outlook 2007 and Access 2007. Your collection process is too far outside the limited boundaries of what it can do.

I've never used data collection but if it's a case of not able to handle multiple table, I suppose we could "cheat" by creating a non-normalized table that composes all 4 tables and let user fill it out and once it's collected, decompose into normalized tables in the database. That would work if this is going to be a one-off thing. If it's a recurring thing, then we may have to look at other option.

In case of constraining dependents, consider adding a field represented as a combobox that would contain one of four possible values, "Dependent #1"..."Dependent #4", then create a Unique index covering both Client ID and the Dependent #, thus guaranteeing that nobody sends 5th or more dependents.

...LOL looks like it's time for me to bow out - this is clearly more advanced stuff than i know at the current time.

glad you guys have come in and know the jargon!

good luck with your project, cbrace09.

I handle things like this much like Banana suggested. I create a "de-normalized" structure for the data collect. It does not have to match the final destinations design. I use use code to place the data into the properly normalized system.

If it we me and I was wanting to be more productive with the man-hours, then I would use a web site to collect the data.