query

[Guest]

I have a table with 2 fields labeled "referenceno" and "referenceno2" and am
using a query to pull data from the table. The query is asking the user to
enter the reference # and it's only pulling half the data. This particular
problem has a reference # of 081207A and 081207B under the referenceno. When
I run the query and the user enters 081207A it works pulling the 10 records
asked for and if the user enters 081207B it works pulling the 10 records
asked for. What I need the user to do is enter 081207A and 081207B so it
pulls all 20 records when the query is run. How do I set the query up for
this to happen and how does the user enter the information.

 

[Jeff Boyce]

Patrick

The data structure you described would be what you'd probably use if you
were limited to working in a spreadsheet. Access is a relational database,
not a spreadsheet. And it isn't clear from your description whether a
single record could have TWO [refno]/[refno2] values, or would only ever
have one?

First, are ALL of your [refno] and [refno2] values identical in the same
record except for the "A" and "B"? Next, could you ever, EVER have more
than "A" and "B" (?say, "C")?

If you only really care about the first 6 characters/digits (and not the
"A", "B", ..."Z" 'suffix'), then have your users enter (or better still,
select from a list) just those 6 characters. You can have Access search for
values "Like [Enter first 6] & *"
to find both "A" and "B" ... but you'd need to have a well-normalized table
that held both refnos in a single field for this to work.

If you absolutely positively are not even considering using a data design
that lets Access better use its relationally-oriented features and
functions, take a look at using a UNION query (see Access HELP).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

-The record could have 2 refno values.
-There can be a C, D and an E besides the A and B.
-The first 6 numbers are actually the date that the product was produced.
So the first 6 numbers will change every day. The A-E are the letters
describing which tank the product came out of.
-The reason there are 2 refno is because they may produce a pallet of
product that contains product produced from both tanks A and B or B and C so
on... If the pallet was a partial pallet with 20 cases from tank A for it
would be 082207A and 20 cases from tank B under refno2 it would be 082207B.
If that pallet has all product from tank A it would be 082207A in refno and
nothing entered in refno2.

Let me know if this helps our how to set this up better.

Patrick

The data structure you described would be what you'd probably use if you
were limited to working in a spreadsheet. Access is a relational database,
not a spreadsheet. And it isn't clear from your description whether a
single record could have TWO [refno]/[refno2] values, or would only ever
have one?

First, are ALL of your [refno] and [refno2] values identical in the same
record except for the "A" and "B"? Next, could you ever, EVER have more
than "A" and "B" (?say, "C")?

If you only really care about the first 6 characters/digits (and not the
"A", "B", ..."Z" 'suffix'), then have your users enter (or better still,
select from a list) just those 6 characters. You can have Access search for
values "Like [Enter first 6] & *"
to find both "A" and "B" ... but you'd need to have a well-normalized table
that held both refnos in a single field for this to work.

If you absolutely positively are not even considering using a data design
that lets Access better use its relationally-oriented features and
functions, take a look at using a UNION query (see Access HELP).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with 2 fields labeled "referenceno" and "referenceno2" and
am
using a query to pull data from the table. The query is asking the user
to
enter the reference # and it's only pulling half the data. This
particular
problem has a reference # of 081207A and 081207B under the referenceno.
When
I run the query and the user enters 081207A it works pulling the 10
records
asked for and if the user enters 081207B it works pulling the 10 records
asked for. What I need the user to do is enter 081207A and 081207B so it
pulls all 20 records when the query is run. How do I set the query up for
this to happen and how does the user enter the information.

 

[John W. Vinson]

I have a table with 2 fields labeled "referenceno" and "referenceno2" and am
using a query to pull data from the table. The query is asking the user to
enter the reference # and it's only pulling half the data. This particular
problem has a reference # of 081207A and 081207B under the referenceno. When
I run the query and the user enters 081207A it works pulling the 10 records
asked for and if the user enters 081207B it works pulling the 10 records
asked for. What I need the user to do is enter 081207A and 081207B so it
pulls all 20 records when the query is run. How do I set the query up for
this to happen and how does the user enter the information.

I think you just need to use OR rather than AND. In the query grid, put the
criteria under the referenceno and referenceno2 fields on separate lines.

I strongly suspect that you should consider restructuring your tables. If
there are two possible reference numbers for a record, might there not someday
be three? Do you actually have a one-to-many or a many-to-many relationship
here?

John W. Vinson [MVP]

 

[Jeff Boyce]

As John V. points out, and your explanation seems to confirm, you have a
one-to-many relationship. In a spreadsheet, you just add columns. In a
relational database, you design your table structure so that you just add
rows.

You might want to check into the topic of "normalization" before you proceed
any further with "how to"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

-The record could have 2 refno values.
-There can be a C, D and an E besides the A and B.
-The first 6 numbers are actually the date that the product was produced.
So the first 6 numbers will change every day. The A-E are the letters
describing which tank the product came out of.
-The reason there are 2 refno is because they may produce a pallet of
product that contains product produced from both tanks A and B or B and C
so
on... If the pallet was a partial pallet with 20 cases from tank A for it
would be 082207A and 20 cases from tank B under refno2 it would be
082207B.
If that pallet has all product from tank A it would be 082207A in refno
and
nothing entered in refno2.

Let me know if this helps our how to set this up better.

Patrick

The data structure you described would be what you'd probably use if you
were limited to working in a spreadsheet. Access is a relational
database,
not a spreadsheet. And it isn't clear from your description whether a
single record could have TWO [refno]/[refno2] values, or would only ever
have one?

First, are ALL of your [refno] and [refno2] values identical in the same
record except for the "A" and "B"? Next, could you ever, EVER have more
than "A" and "B" (?say, "C")?

If you only really care about the first 6 characters/digits (and not the
"A", "B", ..."Z" 'suffix'), then have your users enter (or better still,
select from a list) just those 6 characters. You can have Access search
for
values "Like [Enter first 6] & *"
to find both "A" and "B" ... but you'd need to have a well-normalized
table
that held both refnos in a single field for this to work.

If you absolutely positively are not even considering using a data design
that lets Access better use its relationally-oriented features and
functions, take a look at using a UNION query (see Access HELP).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with 2 fields labeled "referenceno" and "referenceno2"
and
am
using a query to pull data from the table. The query is asking the
user
to
enter the reference # and it's only pulling half the data. This
particular
problem has a reference # of 081207A and 081207B under the referenceno.
When
I run the query and the user enters 081207A it works pulling the 10
records
asked for and if the user enters 081207B it works pulling the 10
records
asked for. What I need the user to do is enter 081207A and 081207B so
it
pulls all 20 records when the query is run. How do I set the query up
for
this to happen and how does the user enter the information.

 

[Guest]

The biggest problem here is that I am very new to this. I have under
criteria in the query [enter pallet no] and there is an or I am looking for
the and. How do I do a selection with an and not an or?

 

[Ronald Marchand]

http://office.microsoft.com/en-us/training/CR061829401033.aspx
Follow this link to MS Training. There are 4 brief lessons on queries that
may help you here.

Ron

The biggest problem here is that I am very new to this. I have under
criteria in the query [enter pallet no] and there is an or I am looking
for
the and. How do I do a selection with an and not an or?

I think you just need to use OR rather than AND. In the query grid, put
the
criteria under the referenceno and referenceno2 fields on separate lines.

I strongly suspect that you should consider restructuring your tables. If
there are two possible reference numbers for a record, might there not
someday
be three? Do you actually have a one-to-many or a many-to-many
relationship
here?

John W. Vinson [MVP]

 

[John W. Vinson]

The biggest problem here is that I am very new to this. I have under
criteria in the query [enter pallet no] and there is an or I am looking for
the and. How do I do a selection with an and not an or?

Please open the query in design view and select View... SQL on the menu. Copy
and paste the cryptic text you'll see to a message here.

Since I don't know anything about the structure or fieldnames of your table
it's hard to be specific - the SQL will give me a better picture of what
you're using.

John W. Vinson [MVP]

 

[Guest]

SELECT [Finished Product Table].Dry_Date, [Finished Product Table].Customer,
[Finished Product Table].[Pallet Number], [Finished Product Table].[Lot
Number], [Finished Product Table].[Lot Number2], [Finished Product
Table].[#TableACases], [Finished Product Table].[#TableBCases], [Finished
Product Table].[#TableCCases], [Finished Product Table].[#TableDCases],
[Finished Product Table].[#TableECases], [Fruit Inventory].[lot-bin#], [Fruit
Inventory].DateProcessed, [Finished Product Table].ReferenceNo, [Finished
Product Table].ReferenceNo2
FROM [Finished Product Table] INNER JOIN [Fruit Inventory] ON [Finished
Product Table].ReferenceNo = [Fruit Inventory].ReferenceNo
WHERE ((([Finished Product Table].[Pallet Number])=[enter pallet #]));

Below is a record from the finished product table and you can see that there
are 2 reference numbers for this record.
Dry_Date Customer Pallet Number Lot Number Lot Number2 8/13/07
2534 0708-13

#TableACases #TableBCases #TableCCases #TableDCases 32
40


#TableECases ReferenceNo ReferenceNo2
081207A 081207B

Here is a record from the fruit inventory table
lot-bin# Grower Name Bin Net Weight DateProcessed
01-01 XXXX 1170.5 8/12/2007
01-02 XXXX 1250
8/12/2007
TableLetter TableLetter2 ReferenceNo ReferenceNo2
A 081207A
A B 081207A 081207B

You can see that there are 2 reference numbers for lot-bin# 3636-26 because
some of the fruit went into table A and some fruit went into table B. I have
10 bins of fruit in table A and 10 bins of fruit in table B and when I run
the query I get the 10 bins of fruit from table A and nothing from table B.

By the way, Thanks for hanging in there with me