Access DB for PayPal Transactions

We need a relatively simple MS Access DB that can import a tab delimited ASCII text file downloaded from PayPal and then divide and move the data to three different related tables named something like tblProducts, tblCustomers, tblOrders, and a reference stand-alone table for words to be ignored in a search.

The tblOrders table should have a one to many relationship with both other tables where the many is on the tblOrders table in both cases.

This data will be mostly eBay sales, but could also be sales from our website or other on-line marketplaces.

The code that performs the import must also perform searches for existing products, customers, and orders to avoid duplication.? The search routine should simply match key words in the Item Title field/column of the data file and IGNORE words and characters including but not limited to "Brand", "New", "Used", "Vintage", "Antique", "pm", "And", "&" "+", "Plus", "With", "w/", "w", "NR", "IN", "A", etc.? The use of these words should be case insensitive.

You must provide a form or some other easy method to allow the user to easily and quickly change the words that are ignored (not used) in the search criteria.

Only one (1) user will use this Access file at one time.

All code must be in MS Access VBA with comments and contained in one (1) MS Access 2003 file.

A different front end written in a different language and compiled is NOT acceptable at this time.

## Deliverables

The Product table should have additional fields such as KeyWords (you may add multiple fields instead of just one field, up to 10, for this if it makes the searching easier) and Cost.? We will enter the product cost later during testing.

The searching of the Product table should also, as necessary, ask the user for confirmation if it finds a possible match on the customer or product tables, before proceeding.? Possible matches should occur when words (not counting ignored words) in the "Item Title" field (text file) match the Key Words listed in the KeyWords field(s) in the Product table.

If a possible match was found in the Product Table, a confirmation form should appear and give the user two choices:? 1) Yes, it is a match? or 2) No , it is not a match.? The form should display the Item Title and key words extracted from it on the first line, and the Product Name and key words from the Product table on the second line.

If the user presses the button "Yes, it is a match", then no new record should be added and the search should continue.? If the user presses the button "No, it is not a match", then a new record should be added in the Product table at that time.

If there was no record found that was a possible match (i.e., not even close to any existing record) then the search process should NOT ask the user for confirmation and the process should just add a new record to the Product table and continue.

In any case, the "Item Title" field from the imported text file should become the Product Name for the new record or overwrite the Product Name in the existing record in the Product table.? Key words extracted from the Item Title (text file) should be put in the the KeyWords field(s) in the product table to provide for matching on the next import.

The searching of the Customer table should be fairly simple.? Compare the customer first name, last name, and postal code (if postal code is blank, use country and city).? If all three are a match, do not add a new record and continue.? If any do not match, add a new customer record and continue.? No confirmation from the user should be necessary.

The searching of order table should be very simple and does not need any user intervention or confirmation.? The PayPal transaction IDs (available for every order) are unique and they are either found in the table or they are not.? If the Trans. ID is found, do not add a new record and continue.? If it is not found in the Order table, add a new record and continue.

All searching should ignore spaces (i.e., use the trim function before or during any comparisons).

A small screen should appear during the data import and search process so that the user can see the progress and what the VBA code is currently doing (e.g. Importing Data from Text File, Searching for Product...).

My recommendation is that the data should be first imported quickly to a separate (possibly temporary) table, and then each record should be analyzed, divided, and moved to the 3 primary tables one records at a time.? However, if you know a faster and reliable process, we will probably accept it, as long as it is efficient and not abnormally slow.

The Access DB should start with a basic switchboard screen showing buttons for the user such as but not limited to:? ? View/Modify? Products,? View/Modify Customers, View/Modify Orders, Import PayPal Data, Close DB, View Queries, View Reports, etc.

The type of reports we need for this project are fairly simple:

1.? Order Report sorted by Country (nation) with subtotals for all numeric fields and count for each country in each section, plus totals at the end of the report.? Include Subject, Date/Time, Customer First & Last Name, Postal Code, Country, Price (subtract all other currency fields from the Gross amount), PayPal Fee, Shipping & Handling, Shipping Insurance, and Sales Tax fields.? The user should be prompted to enter a begin and end date to filter the report.? If possible, include a percentage for the Price Subtotal of each country section, compared to the grand total for the report.

2.? Very similar to #1 except filter for USA customers only, remove the country field, add the City and State fields, and divide the report by State instead of country.

3.? Product list with the product name, total number sold, average price, cost, Gross Profit (AVG price - cost), margin% ((AVG price - cost)/AVG price), lowest price, and highest price.

All Reports should be based on Queries that can be run separately by the user, as much as possible.? That is, minimize the use of hard coded SQL inside the VBA code.

The data import process should start with the push of the Import PayPal Data button and then it should ask the user to select a file (standard Windows open file dialog box) and? NOT assume a name or location.? This file must be downloaded manually before the import process can start.

At some point during the import and moving process, the code must examine the data to determine if it should be kept or deleted/ignored.? Certain types of transactions should be ignored and not kept or moved to any of the three permanent tables.? For example, refunds received from the USPS and dividend (interest)? payments from PayPal are not orders and should be ignored.? It is up to you, the programmer to decided the best time and place in the code to do this data validation test.? we will provide a detailed list of all types of records that must be ignored.

The types of transaction that should be ignored and not permanently recorded are where the word"Sent" appears in the "Type" field, The amount in the "Gross" field is <= 0, "Item Title" field is blank, OR if the "Country" field is blank.

We will provide an Excel Spreadsheet file which clearly illustrates what? fields must be kept (recorded) and examplels of which type of records should be ignored (not kept).? This spreadsheet will be color coded to show which fields (columns) should go to which table in the Access database.

The forms? View/Modify? Products,? View/Modify Customers, and View/Modify Orders should allow the user to manually view, add new records, edit existing records, or delete records in the related tables.? The Delete function should request confirmation from the user before deleting any record in any table, no exception.

All forms (ones that you build and are permanent) should have a uniform appearance (i.e., build them all with the same standards, execept any simple/basic message or input boxes over which you do not have any design control.? Put headings, lables, and? command buttons? in Bold (maybe a little bit larger font also, space permitting).? Make the font larger for any headings and larger still for the form title.? Use common sense when selecting fonts (we recommend Time New Roman or Ariel) and their sizes.? The font size should be large enough to read easily on a 17 or 19 inch monitor, but not so large to be annoying.? We recommend a fornt size of 10 to 14 for most text.? Don't use a gigantic size (e.g., larger than 24 pt) for anything, even titles.? Titles should never wrap into 2 lines.? We are not very picky about the color scheme.? We suggest perhaps a combination of Navy or Cobalt Blue (for the borders) and light yellow, light gray,? or light blue? (for the background/body)? or almost anything that has contrast and makes text easy to read without eye strain.? The color scheme should be clean, SIMPLE, and relatively subtle and not look stand out or look like it is trying to win a design contest.? For the text boxes (as necessary), we prefer plain black text against a white or light yellow background.? The text box background color? should be different than the surrounding form background color.? However,? DO? NOT spend too much time on the "look and feel" of the forms.? It is not that critical and we are open to suggestions.

Referential Integrity must be enforced for all tables that have relations.

All VBA code must commented (within reason) and and all variables must be named in some standard method to allow a different programmer to understand the code and make changes with significant difficulty.

I recommend using Hungarian notation for naming variables and all Access objects.? Also Use common/standard naming prefixes? for variables and objects (e.g. tbl = table, qry =query, rpt = report, str = string variable, int = integer variable, etc.).

Code modules, subroutines, and functions must be named appropriately so that it is obvious what they do at a high level.

Avoid "hard coding" and constants in the VBA code.? Instead, set constants in the header (public if necessary) and then set the variables equal to the constants in code as necessary.? Also, avoid hard coding SQL as much as possible.? Instead, reference queries.

All variables must be declared (dimensioned) and the Option Explicit statement must be used in all VBA code modules.

All Access objects (e.g., queries, tables, reports, macros, modules, etc.) must be given names that make sense.? The name should make its purpose? very clear and specific.

We will provide a real example text file for development and testing purposes.? This file will be real data from our account on PayPal from a few years ago.

We will provide an Excel spreadsheet displaying the fields (columns that will be kept and the types of records that should be ignored.? We also provide, in the same Excel file, mapping between the Import text data file and the database tables (i.e. the data in this column in the text file goes to this field in this table in the DB).? If necessary, we can provide a skeleton (empty) MS Access DB file to show the tables we prefer and their field names.

The completed MS Access File Must pass our user acceptance testing with the most recent tab delimited files from PayPal.? We reserve the right to NOT share the most recent PayPal data.? The format of the old data is the same as new data.? The content is the only difference.

Μηχανολογία Excel Microsoft Access Microsoft Exchange MySQL PHP Powerpoint Αρχιτεκτονική Λογισμικού Δοκιμή Λογισμικού Visual Basic για Εφαρμογές Word

Ταυτότητα Εργασίας: #3612508

Σχετικά με την εργασία