RE: Setting up query with multiple data

Tech-Archive recommends: Fix windows errors by optimizing your registry

Sorry for not getting back sooner. I have run the query. The results hide
the "Carrier number" I requested and list all the "item" numbers related
not related to the requested "Carrier number". This includes all the other
"Carrier numbers". I can not see where I went wrong.

"KARL DEWEY" wrote:

> Sorry about that. That was the name I gave the first query.
>
> "Nick" wrote:
>
> > I am having problems with the CarrierNumDate.date on the second query. Error
> > message states it can not find it. First query works great!
> > "KARL DEWEY" wrote:
> >
> > > 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: #Error when using IIF statement in a query
    ... KARL DEWEY wrote: ... complete them but I think I read that Access take all queries as if one and ... An alternative is the make the first query a make-table to create a ... When the IIF statement evaluated false, ...
    (microsoft.public.access.queries)
  • RE: Same Name with Multiple IDs
    ... Table1_A is the name I gave to the first query. ... KARL DEWEY wrote: ... because it's an old request, I thought to re-post it. ...
    (microsoft.public.access.queries)
  • RE: Query Criteria
    ... KARL DEWEY wrote: ... Use this as first query -- ... FROM CountNumber, Holiday ...
    (microsoft.public.access.queries)