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-30 : 13:47:42
|
| Terry 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 2-14-1948D 1952 10G010V123 025 06001 08000 6-25-1952M 1975 30C525P321 125 14001 16000 10-01-1975SB 1987 29C525P321 500 04001 06000 10-01-1987DL 1993 31C525P341 014 20001 22000 10-01-1993FB 2005 31C525P362 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-2006Requirements: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 Loc Box No M 1975 15023 Doe John E 30C525P321 125Bgn Cert No End Cert No Shipped Date14001 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:1. Location Table displayed above2. The data entry form used to enter the criteria to lookup in location Table3. Create the Retrieved Data Table4. Most importantly the query, module, or SQL to make it all work?Man in need of helpt.c.westover@sbcglobal.net" |
|
|
|
|
|
|
|
MS Access Append, Lookup, parameter table