Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-01-25 : 08:21:34
|
| Terry Westover writes "Overview of the situation: Serialized Certificates are stored in a warehouse within boxes each box containing approximately 2000 certificates. .There are 800 boxes stored in 50 warehouse locations. Each certificate has a person’s last name, first name and middle initial (Doe, John, E.). Each certificate has a year issued, (e.g. 1985). Each certificate has a five digit (numeric) certificate number.Presently hard copy books are used to locate a specific certificate. Data fields within the hard copy books are:Event Year Whse No Box No Bgn Cert No End Cert NoShip DateB 1948 12B002K004 001 00001 02000 1-14-1948D 1952 10G010V123 025 06001 08000 6-25-1952M 1975 30C525P321 125 14001 16000 12-01-1975SB 1987 30C525P321 500 04001 06000 10-01-1987DL 1993 30C525P321 014 20001 22000 12-01-1993FB 2005 30C525P321 066 32001 34000 10-01-2005Update:Data from hard copy books have been entered into an MS Access Table named Location TableSource Document to enter search criteria is:Event Year Cert No Last Name, First Name MI Query DateM 1975 15023 Doe John E 02-25-2006The actual search criteria is Event, Year and Cert No. The other four fields need to be entered and then need to be sent to the Retrieval append table below.Requirements:a. MS Access Query Form to enter criteria to search the Location Table to accomplish two tasks: 1. Match the record in the Location table to the Event, Year and Certificate Number that lies between the ranges of the Bgn Cert No Field (Beginning) and the End Cert No Field (Ending). 2. Append the querying result data to the retrieve data table:Event, Year, Cert No Last Name First Name, MI Whse No, Box No, Bgn Cert No End Cert No, Shipped Date, Query Date. Sort by Query Date, Event, Last Name, First Name, MI, Using the source document search criteria the Retrieved data Table would contain the following data:Event Year Cert No Last N First N MI Whse M 1975 15023 Doe John E 30C525P321 Box No Bgn Cert No End Cert No Shipped ate 125 14001 16000 10-01-1975The results of the query used data retrieved from the Location Table and data from the data entry form (Last Name, First Name, MI, Query Date) to append the Retrieved Data Table.So using MS Access version (2000) and the criteria presented above, can anyone out there , create the following:1. Location Table displayed above2. The data entry form used to enter the criteria to lookup in the entered criteria from in the Location Table3. Create the Retrieved Data Apppend Table4. Most importantly the query, module, or SQL to make it all work?Man in need of helpt.c.westover@sbcglobal.net" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-01-25 : 08:22:02
|
| Sorry, we don't answer homework or exam questions on SQL Team. |
 |
|
|
|
|
|
|
|
MS Access Question