Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 09-21-2009, 11:33 AM   #1
cbrace09
Registered User
 
Join Date: Jun 2009
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
cbrace09 is on a distinguished road
Exclamation 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!

cbrace09 is offline   Reply With Quote
Old 09-21-2009, 08:23 PM   #2
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

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

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wiklendt is offline   Reply With Quote
Old 09-22-2009, 08:09 AM   #3
cbrace09
Registered User
 
Join Date: Jun 2009
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
cbrace09 is on a distinguished road
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

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.

cbrace09 is offline   Reply With Quote
Old 09-22-2009, 08:17 AM   #4
mcclunyboy
Newly Registered User
 
Join Date: Sep 2009
Posts: 292
Thanks: 6
Thanked 4 Times in 4 Posts
mcclunyboy is on a distinguished road
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

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.
mcclunyboy is offline   Reply With Quote
Old 09-22-2009, 01:38 PM   #5
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

Quote:
Originally Posted by cbrace09 View Post
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.
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?
__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wiklendt is offline   Reply With Quote
Old 09-24-2009, 12:26 PM   #6
cbrace09
Registered User
 
Join Date: Jun 2009
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
cbrace09 is on a distinguished road
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

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 is offline   Reply With Quote
Old 09-24-2009, 02:53 PM   #7
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

Quote:
Originally Posted by cbrace09 View Post
Please read through the posting carefully and you will understand what I have already done and what I want to do.

Thanks for looking!
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!

__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by wiklendt; 09-24-2009 at 02:59 PM.
wiklendt is offline   Reply With Quote
Old 09-24-2009, 02:56 PM   #8
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,820 Times in 1,576 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 09-24-2009, 03:03 PM   #9
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

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.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 09-24-2009, 03:10 PM   #10
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

...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.
__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wiklendt is offline   Reply With Quote
Old 09-24-2009, 04:03 PM   #11
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,254
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Data Collection Replies in Access 2007? Or is my layout wrong?

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.

__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
Reply

Tags
access 2007 , data collection , data collection replies

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Command Line Switches KenHigg Access FAQs 0 07-22-2009 05:57 PM
Removing Security sanmisds General 3 05-21-2009 01:38 AM
Updating Access Database With New Excel Data Jakz34 General 5 02-11-2009 06:49 AM
Input Data using Access 2007 Runtime Rats Forms 2 10-16-2008 05:35 PM
Question Collect Data Access 2007: How do you add a mail-merge field into the introduction? RuggeroSB General 2 09-23-2008 06:35 AM




All times are GMT -8. The time now is 02:41 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World