RE: Setting up query with multiple data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Two queries --
SELECT Table11.labelid, Table11.date
FROM Table11
GROUP BY Table11.labelid, Table11.date
HAVING (((Table11.labelid)=[Enter Carrier number]));

SELECT Table11.labelid, Table11.date, Table11.time, CarrierNumDate.date
FROM Table11, CarrierNumDate
WHERE (((Table11.labelid)<>[CarrierNumDate].[labelid]) AND
((Table11.date)=[CarrierNumDate].[date]));


"Nick" wrote:

> The "Carrier Number" is any number that begins with 9182037644478802*
>
> Date stamp
> 12/20/2004
> Carrier Number
> 9182037644478802031452
> Item
> Time Stamp
> 03041560000413075333 01:06:42
> 9101932495864311932318 01:07:27
>
> and so on.
> There are 77 different Carrier Number and hundreds of item number scanned
> per day.
> I need to report the "item"s scanned with there "time stamp" for each
> "Carrier Number" for any given date.
> Thank you,
> "KARL DEWEY" wrote:
>
> > So what would be the "Carrier Number" in this data that you are looking for?
> > 03041560000413075333 12/20/2004 01:06:42
> > 9182037644478802031452 12/20/2004 01:06:42
> > 9182037644478802031117 12/20/2004 01:06:56
> > 03041560000028421006 12/20/2004 01:06:56
> > 9101932495864311932318 12/20/2004 01:07:27
> > 9182037644478802031452 12/20/2004 01:07:27
> > 9182037644478802031315 12/20/2004 01:07:40
> >
> > Are you looking for the date/time stamp assigned to a particular item and
> > then want to extract all items with the same stamp?
> >
> > "Nick" wrote:
> >
> > > I am trying to determine all the entries for all "carrier number". The Co4
> > > string was simplified in part because I was not sure how to explain it but
> > > the concept is the same. Here in lies the problem with the "Item" . and the
> > > ("Carrier No" or Enter search item). They can scann one item and a "Carrier
> > > No" or a number of "items"and in no paticular order Including the "Carrier
> > > No" but there is always a "carrier number" as one of the items scanned. The
> > > file is a compilation of all scanning equipment throught out the company.
> > > What I would like to do is sort all "Item" number by the "Carrier No".
> > > The only thing that actually ties the "Item" and "Carrier No" together is
> > > the time the scanner records when the first item or Carrier No is scanned. It
> > > date stamps everything with a single time.
> > > Thanks for your patience. I hope this helps.
> > > "KARL DEWEY" wrote:
> > >
> > > > This data looks nothing like what you started with when you were searching
> > > > for "C04" in the string.
> > > >
> > > > What are you trying to do with this data?
> > > >
> > > > "Nick" wrote:
> > > >
> > > > > I have made correction and am still unable to get it to work. I have
> > > > > included a sample of the file. 9182037644478802031452 is the actual serch
> > > > > item. There are 5 entries.
> > > > > query does not work for me. Thanks for your time
> > > > > label_id date
> > > > > time
> > > > > 03041560000413075333 12/20/2004 01:06:42
> > > > > 9182037644478802031452 12/20/2004 01:06:42
> > > > > 9182037644478802031117 12/20/2004 01:06:56
> > > > > 03041560000028421006 12/20/2004 01:06:56
> > > > > 9101932495864311932318 12/20/2004 01:07:27
> > > > > 9182037644478802031452 12/20/2004 01:07:27
> > > > > 9182037644478802031315 12/20/2004 01:07:40
> > > > > 420802039101010742567000288901 12/20/2004 01:07:40
> > > > > 02946198744085608593 12/20/2004 01:08:29
> > > > > 9182037644478802031520 12/20/2004 01:08:29
> > > > > 9182037644478802031452 12/20/2004 01:08:45
> > > > > 03040370000165834327 12/20/2004 01:08:45
> > > > > 9182037644478802031513 12/20/2004 01:08:58
> > > > > 9102001206742060225991 12/20/2004 01:08:58
> > > > > 9182037644478802031452 12/20/2004 01:09:11
> > > > > 420802039102026535096024627362 12/20/2004 01:09:11
> > > > > 9102001680594000116481 12/20/2004 01:10:15
> > > > > 9182037644478802031452 12/20/2004 01:10:15
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "KARL DEWEY" wrote:
> > > > >
> > > > > > You have a typo. Below corrects the typo.
> > > > > >
> > > > > > WHERE ((([ALID0001].[LABEL_ID]) Like "*" & [Enter date]& "*" and
> > > > > > (ALID0001.LABEL_ID) LIKE "*"& [Enter your search items] & "*"));
> > > > > >
> > > > > > "Nick" wrote:
> > > > > >
> > > > > > > I used your example as a guide line, the query askes for the "enter your
> > > > > > > search items", Enter date, and then it asks for ALID0001. LABLE_ID.
> > > > > > > Please review my query and tell me where I made my error.
> > > > > > > Thanks,
> > > > > > > SELECT ALID0001.date, [Enter your search items] AS [search items],
> > > > > > > Right([label_id],Len([Label_id])-53) AS Items
> > > > > > > FROM ALID0001
> > > > > > > WHERE ((([ALID0001].[LABEL_ID]) Like "*" & [Enter date]& "*" and
> > > > > > > (ALID0001.LABLE_ID) LIKE "*"& [Enter your search items] & "*"));
> > > > > > >
> > > > > > > "KARL DEWEY" wrote:
> > > > > > >
> > > > > > > > This should do it --
> > > > > > > > SELECT [Enter date - 01/02/04] AS [Date scanned], [Enter your search item -
> > > > > > > > C04] AS [Search item], Right([YourDataField],Len([YourDataField])-53) AS Items
> > > > > > > > FROM YourTable
> > > > > > > > WHERE (((YourTable.YourDataField) Like "*" & [Enter date - 01/02/04] & "*"
> > > > > > > > And (YourTable.YourDataField) Like "*" & [Enter your search item - C04] &
> > > > > > > > "*"));
> > > > > > > >
> > > > > > > >
> > > > > > > > "Nick" wrote:
> > > > > > > >
> > > > > > > > > I am unable to seperate scanns or the tables. this is an imported file and
> > > > > > > > > the way you see it is the way it comes in. Somewhere within the scanned
> > > > > > > > > items will be the C0* record. I am at a loss.
> > > > > > > > >
> > > > > > > > > "KARL DEWEY" wrote:
> > > > > > > > >
> > > > > > > > > > Your table is wrong. Use a separate entry for each item scaned. Two ways to
> > > > > > > > > > do it.
> > > > > > > > > > tblScanItems--
> > > > > > > > > > Item - text field
> > > > > > > > > > ScanDate - datetime field
> > > > > > > > > > Optional
> > > > > > > > > > ScanTime - datetime field
> > > > > > > > > > Scanner - text - who done it
> > > > > > > > > >
> > > > > > > > > > The other way.
> > > > > > > > > > tblScan--
> > > > > > > > > > ScanID - autonumber - Primary Key
> > > > > > > > > > ScanDate - datetime
> > > > > > > > > > Scanner - text
> > > > > > > > > >
> > > > > > > > > > tblScanItems--
> > > > > > > > > > ScanID - number - long interger
> > > > > > > > > > Item - text field
> > > > > > > > > >
> > > > > > > > > > Create a one-to many relation tblScan.ScanID to tblScanItems.ScanID
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Nick" wrote:
> > > > > > > > > >
> > > > > > > > > > > I want to sort by the "C04" part of the records. The data connected to the
> > > > > > > > > > > Datestamp and DateScanned can be a string of entries or just one, with the
> > > > > > > > > > > "C*" record in it.
> > > > > > > > > > > Example
> > > > > > > > > > > Datestamp 11:00 DateScanned 08/10/05 Items scanned C04, 3333, 222, and 888
> > > > > > > > > > > Datestamp 12:00 DateScanned 08/10/05 Items scanned C03, 3030, and 202
> > > > > > > > > > > Datestamp 12:01 DateScanned 08/10/05 Items scanned 1010, and C04
> > > > > > > > > > > Datestamp 1:00 DateScanned 08/10/05 Items scanned 1111, C04, and 20
> > > > > > > > > > > Datestamp 1:20 DateScannec 08/10/05 Items scanned C40, 77777, and 15
> > > > > > > > > > > I am not sure what other information is required to get this to work.
> > > > > > > > > > > but in this example, the query should show a date of 08/10/05; C04, with the
> > > > > > > > > > > list of Items scanned; 3333,222,888,1010,1111,20,77777 and 15.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
.

Relevant Pages

  • Re: 10c postage
    ... Ten cents postage. ... Several possible reasons for only the 10 cent stamp ... and including being cased for your mail carrier was mechanically done. ... pre-sorted mail in the Domestic Mail Manual. ...
    (rec.collecting.stamps.discuss)
  • RE: Setting up query with multiple data
    ... The "Carrier Number" is any number that begins with 9182037644478802* ... Time Stamp ... >> the time the scanner records when the first item or Carrier No is scanned. ...
    (microsoft.public.access.queries)
  • Re: AT&Ts proposed filtering policy is bad news
    ... online network in what he claims is an effort to stamp out theft of ... their backbone is a common ... carrier and they're not responsible for any of the content. ...
    (misc.news.internet.discuss)
  • RE: Setting up query with multiple data
    ... then want to extract all items with the same stamp? ... "Nick" wrote: ... > the time the scanner records when the first item or Carrier No is scanned. ...
    (microsoft.public.access.queries)
  • RE: Setting up query with multiple data
    ... I am having problems with the CarrierNumDate.date on the second query. ... > FROM Table11, CarrierNumDate ... >> Time Stamp ... >> There are 77 different Carrier Number and hundreds of item number scanned ...
    (microsoft.public.access.queries)