Not getting results I need

J

Joan

Hi,
Could someone please help me with structuring a query for a subform? I
have been working with these queries for weeks and they are about to get the
best of me.
To sum up as best as I can, what I need is a query for a sub-form
(called EditDog) on the EditDeleteInvoice form. I am querying for dog sales
that have already been invoiced. The tricky part is that sometimes a dog
may be returned by the customer(store) because it is sick or injured. In
these cases, the dog is taken back into inventory and when it is healed, it
can be resold. The store it is resold to may be different from the first
sale, as may the saleprice. Below is the relevant part of my table
structure:

DOGS Table:
Dog Number (Primary Key)
Litter Number
Weight
Store
Store1 (Field where salesmen enter the CustomerID when they sell a dog.
Code behind the AfterUpdate event of this bound control
then enters the value in either Store or ReturnedStore
depending upon whether this is a first time sale or second time sale.)
Salesperson
SalesPrice
SalesPrice1 (Field where salesmen enter the sales price when they sell a
dog. Code behind the AfterUpdate event of this bound control
then enters the value in either SalesPrice or
ReturnedSalesPrice depending upon where this is a first time sale or a
second time sale.)
Received Date (Received into inventory from Breeder)
etc.

INVOICES Table:
Invoice Number (Primary Key)
Type (Type of transaction: INV for Invoice, CR for Credit, DB for
Debit, Adj for Adjustment)
Store (Store (or Customer) that transaction applies to)
DateSold (Date of Transaction)
Debit
Credit
Dog Number (Dog Number that a Credit Memo(CR) or Debit Memo (DB)applies
to. NOT for Dogs that are sold)
Adjcode (Code used for any Adj or CR: R for Returned, D for Death, C for
Congenital, M for Misc)
Comment
Shipper
On Invoice (Invoice Number that the Debit or Credit is applied on)

SALES Table:
Invoice Number (Primary Key)
Dog Number (Primary Key)
Returned ("Y" if Dog from this sale instance was returned, Null if
not)
ReturnedDate (Date that the dog was returned to us)
ReturnedComment (Reason dog was returned)
ReturnedStore (Store that a returned dog was sold to the second time
the dog sold)
ReturnedSalePrice (Price that a returned dog was sold at the second
time the dog sold)
ReturnedSaleDate (Date that a returned dog was sold on the second time
the dog sold)
ReturnedInvoice (Invoice Number for the second sale of a returned dog)

CUSTOMERS Table:
CustomerID (Primary Key)
CustName (Name of Customer or Store)
Address
City
ST
ZIP
Phone, etc

LITTERS Table:
Litter Number (Primary Key)
Breed Code
BreederCode
Whelped Date
Litter Reg Number
Male Number
Female Number, etc.

RELATIONSHIPS:
TABLE (Linking field) TABLE (Linking
field)
CUSTOMERS(CustomerID): 1: M : INVOICES (Store)
DOGS (Dog Number) : 1:M : SALES (Dog Number)
INVOICE (Invoice Number): 1:M : SALES (Invoice Number)
LITTERS (Litter Number): 1:M : DOGS (Litter Number)
CUSTOMERS(CustomerID): 1:M : DOGS (Store)

There are two different sets of sales that I need. The dogs that
were sold for the first time and the dogs that were sold for the second time
for each invoice number. I've been able to get the dogs that were sold for
the second time by having the Sales table query itself. In the first query,
I started with the Sales table and equi-joined a copy of the Sales table on
Invoice Number = ReturnedInvoice.:
SELECT Sales2nd.[Invoice Number], Sales1st.ReturnedSalePrice,
Sales2nd.[Dog Number], Sales1st.ReturnedStore, Sales1st.ReturnedInvoice,
Sales1st.ReturnedSaleDate
FROM Sales AS Sales2nd INNER JOIN Sales AS Sales1st ON Sales2nd.[Invoice
Number] = Sales1st.ReturnedInvoice;

From there I was able in subsequent queries to get the other fields that I
needed for dogs sold for the second time set. My problem is getting the set
of sales for dogs sold for the first time. In the Sales table there could
be two sale records for the same dog. See the example:

Inv # Dog # Returned ReturnedDate ReturnedComment
ReturnedStore ReturnedSalePrice ReturnedSaleDate ReturnedInvoice
395 1227 Y 12/17/2003 Injured leg
WIZ $215.00 12/19/2003 399
399 1227

However, there are also records for dogs that have only been sold once and
they don't have any of the returned fields filled in. So I cannot query for
records where the returned fields are Not Null. I would so so appreciate
any help with this. It is driving me crazy.

Joan
 
T

Tom Ellison

Dear Joan:

To track each dog and each sale, with possible return, would seem to
require a table design more appropriate to the task.

A table to record a sale might show:

DogNumber
CustomerID
DateSold
SalesmanID
SalePrice
Weight

I've included weight since the dog's weight might change between
sales.

With something like this as a separate table, you can sell and re-sell
a dog as many times as needed.

Showing returns could be done in a separate table for returns, or it
could be recorded in the same table. I would tend toward putting it
in the same table, adding a column for TransactionType which would be
"Sale" or "Return". Another alternative would be to just have a
ReturnDate column in that table, which would be NULL if the dog has
not been returned. However, this would mean you could not record the
weight of the dog at the time it is returned without adding another
column for that (I don't know if that's any issue.)

There seems to be more to your questions, but I'd like to handle
things one at a time.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi,
Could someone please help me with structuring a query for a subform? I
have been working with these queries for weeks and they are about to get the
best of me.
To sum up as best as I can, what I need is a query for a sub-form
(called EditDog) on the EditDeleteInvoice form. I am querying for dog sales
that have already been invoiced. The tricky part is that sometimes a dog
may be returned by the customer(store) because it is sick or injured. In
these cases, the dog is taken back into inventory and when it is healed, it
can be resold. The store it is resold to may be different from the first
sale, as may the saleprice. Below is the relevant part of my table
structure:

DOGS Table:
Dog Number (Primary Key)
Litter Number
Weight
Store
Store1 (Field where salesmen enter the CustomerID when they sell a dog.
Code behind the AfterUpdate event of this bound control
then enters the value in either Store or ReturnedStore
depending upon whether this is a first time sale or second time sale.)
Salesperson
SalesPrice
SalesPrice1 (Field where salesmen enter the sales price when they sell a
dog. Code behind the AfterUpdate event of this bound control
then enters the value in either SalesPrice or
ReturnedSalesPrice depending upon where this is a first time sale or a
second time sale.)
Received Date (Received into inventory from Breeder)
etc.

INVOICES Table:
Invoice Number (Primary Key)
Type (Type of transaction: INV for Invoice, CR for Credit, DB for
Debit, Adj for Adjustment)
Store (Store (or Customer) that transaction applies to)
DateSold (Date of Transaction)
Debit
Credit
Dog Number (Dog Number that a Credit Memo(CR) or Debit Memo (DB)applies
to. NOT for Dogs that are sold)
Adjcode (Code used for any Adj or CR: R for Returned, D for Death, C for
Congenital, M for Misc)
Comment
Shipper
On Invoice (Invoice Number that the Debit or Credit is applied on)

SALES Table:
Invoice Number (Primary Key)
Dog Number (Primary Key)
Returned ("Y" if Dog from this sale instance was returned, Null if
not)
ReturnedDate (Date that the dog was returned to us)
ReturnedComment (Reason dog was returned)
ReturnedStore (Store that a returned dog was sold to the second time
the dog sold)
ReturnedSalePrice (Price that a returned dog was sold at the second
time the dog sold)
ReturnedSaleDate (Date that a returned dog was sold on the second time
the dog sold)
ReturnedInvoice (Invoice Number for the second sale of a returned dog)

CUSTOMERS Table:
CustomerID (Primary Key)
CustName (Name of Customer or Store)
Address
City
ST
ZIP
Phone, etc

LITTERS Table:
Litter Number (Primary Key)
Breed Code
BreederCode
Whelped Date
Litter Reg Number
Male Number
Female Number, etc.

RELATIONSHIPS:
TABLE (Linking field) TABLE (Linking
field)
CUSTOMERS(CustomerID): 1: M : INVOICES (Store)
DOGS (Dog Number) : 1:M : SALES (Dog Number)
INVOICE (Invoice Number): 1:M : SALES (Invoice Number)
LITTERS (Litter Number): 1:M : DOGS (Litter Number)
CUSTOMERS(CustomerID): 1:M : DOGS (Store)

There are two different sets of sales that I need. The dogs that
were sold for the first time and the dogs that were sold for the second time
for each invoice number. I've been able to get the dogs that were sold for
the second time by having the Sales table query itself. In the first query,
I started with the Sales table and equi-joined a copy of the Sales table on
Invoice Number = ReturnedInvoice.:
SELECT Sales2nd.[Invoice Number], Sales1st.ReturnedSalePrice,
Sales2nd.[Dog Number], Sales1st.ReturnedStore, Sales1st.ReturnedInvoice,
Sales1st.ReturnedSaleDate
FROM Sales AS Sales2nd INNER JOIN Sales AS Sales1st ON Sales2nd.[Invoice
Number] = Sales1st.ReturnedInvoice;

From there I was able in subsequent queries to get the other fields that I
needed for dogs sold for the second time set. My problem is getting the set
of sales for dogs sold for the first time. In the Sales table there could
be two sale records for the same dog. See the example:

Inv # Dog # Returned ReturnedDate ReturnedComment
ReturnedStore ReturnedSalePrice ReturnedSaleDate ReturnedInvoice
395 1227 Y 12/17/2003 Injured leg
WIZ $215.00 12/19/2003 399
399 1227

However, there are also records for dogs that have only been sold once and
they don't have any of the returned fields filled in. So I cannot query for
records where the returned fields are Not Null. I would so so appreciate
any help with this. It is driving me crazy.

Joan
Click to expand...
 
J

Joan

Tom,
Thanks for replying to my post.
I would be willing to explore further the route of setting up a whole new
table to track each sale but the problem is this database is large with many
forms, reports and queries. I'm afraid it may not be worth the effort to go
back and have to restructure everything. In fact this is the last thing I
think I have to resolve before saying "It's done". With regard to the sale
of the dogs, there are many forms and reports that tie into this. To further
complicate the situation, the client is already using it. The bug that
needs resolving basically is that when a dog is resold, the new saleprice
does not appear on the EditDeleteDog form, the Invoice Statement or any
related summary reports. The old saleprice is listed or used instead.

I'm not sure how the table you described would link or relate to all of my
other tables. The dog's change in weight is not an issue. Basically, this
is just recorded at the time they enter inventory and most dogs are sold
within a week or two. I had initially hoped that I could get a query to
somehow return the new sale price. Or.....would moving all of the
"Returned" fields to the Dogs table work better than what I presently have?
I could do this and not have to redo too terribly much of the database I
think. I guess I am trying to determine the best trade-off. I already have
a ReturnedDate column in the Sales table that I could move to the Dogs table
to determine if the Dog has been returned or not. What do you think?

Joan


Tom Ellison said:
Dear Joan:

To track each dog and each sale, with possible return, would seem to
require a table design more appropriate to the task.

A table to record a sale might show:

DogNumber
CustomerID
DateSold
SalesmanID
SalePrice
Weight

I've included weight since the dog's weight might change between
sales.

With something like this as a separate table, you can sell and re-sell
a dog as many times as needed.

Showing returns could be done in a separate table for returns, or it
could be recorded in the same table. I would tend toward putting it
in the same table, adding a column for TransactionType which would be
"Sale" or "Return". Another alternative would be to just have a
ReturnDate column in that table, which would be NULL if the dog has
not been returned. However, this would mean you could not record the
weight of the dog at the time it is returned without adding another
column for that (I don't know if that's any issue.)

There seems to be more to your questions, but I'd like to handle
things one at a time.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi,
Could someone please help me with structuring a query for a subform? I
have been working with these queries for weeks and they are about to get the
best of me.
To sum up as best as I can, what I need is a query for a sub-form
(called EditDog) on the EditDeleteInvoice form. I am querying for dog sales
that have already been invoiced. The tricky part is that sometimes a dog
may be returned by the customer(store) because it is sick or injured. In
these cases, the dog is taken back into inventory and when it is healed, it
can be resold. The store it is resold to may be different from the first
sale, as may the saleprice. Below is the relevant part of my table
structure:

DOGS Table:
Dog Number (Primary Key)
Litter Number
Weight
Store
Store1 (Field where salesmen enter the CustomerID when they sell a dog.
Code behind the AfterUpdate event of this bound control
then enters the value in either Store or ReturnedStore
depending upon whether this is a first time sale or second time sale.)
Salesperson
SalesPrice
SalesPrice1 (Field where salesmen enter the sales price when they sell a
dog. Code behind the AfterUpdate event of this bound control
then enters the value in either SalesPrice or
ReturnedSalesPrice depending upon where this is a first time sale or a
second time sale.)
Received Date (Received into inventory from Breeder)
etc.

INVOICES Table:
Invoice Number (Primary Key)
Type (Type of transaction: INV for Invoice, CR for Credit, DB for
Debit, Adj for Adjustment)
Store (Store (or Customer) that transaction applies to)
DateSold (Date of Transaction)
Debit
Credit
Dog Number (Dog Number that a Credit Memo(CR) or Debit Memo (DB)applies
to. NOT for Dogs that are sold)
Adjcode (Code used for any Adj or CR: R for Returned, D for Death, C for
Congenital, M for Misc)
Comment
Shipper
On Invoice (Invoice Number that the Debit or Credit is applied on)

SALES Table:
Invoice Number (Primary Key)
Dog Number (Primary Key)
Returned ("Y" if Dog from this sale instance was returned, Null if
not)
ReturnedDate (Date that the dog was returned to us)
ReturnedComment (Reason dog was returned)
ReturnedStore (Store that a returned dog was sold to the second time
the dog sold)
ReturnedSalePrice (Price that a returned dog was sold at the second
time the dog sold)
ReturnedSaleDate (Date that a returned dog was sold on the second time
the dog sold)
ReturnedInvoice (Invoice Number for the second sale of a returned dog)

CUSTOMERS Table:
CustomerID (Primary Key)
CustName (Name of Customer or Store)
Address
City
ST
ZIP
Phone, etc

LITTERS Table:
Litter Number (Primary Key)
Breed Code
BreederCode
Whelped Date
Litter Reg Number
Male Number
Female Number, etc.

RELATIONSHIPS:
TABLE (Linking field) TABLE (Linking
field)
CUSTOMERS(CustomerID): 1: M : INVOICES (Store)
DOGS (Dog Number) : 1:M : SALES (Dog Number)
INVOICE (Invoice Number): 1:M : SALES (Invoice Number)
LITTERS (Litter Number): 1:M : DOGS (Litter Number)
CUSTOMERS(CustomerID): 1:M : DOGS (Store)

There are two different sets of sales that I need. The dogs that
were sold for the first time and the dogs that were sold for the second time
for each invoice number. I've been able to get the dogs that were sold for
the second time by having the Sales table query itself. In the first query,
I started with the Sales table and equi-joined a copy of the Sales table on
Invoice Number = ReturnedInvoice.:
SELECT Sales2nd.[Invoice Number], Sales1st.ReturnedSalePrice,
Sales2nd.[Dog Number], Sales1st.ReturnedStore, Sales1st.ReturnedInvoice,
Sales1st.ReturnedSaleDate
FROM Sales AS Sales2nd INNER JOIN Sales AS Sales1st ON Sales2nd.[Invoice
Number] = Sales1st.ReturnedInvoice;

From there I was able in subsequent queries to get the other fields that I
needed for dogs sold for the second time set. My problem is getting the set
of sales for dogs sold for the first time. In the Sales table there could
be two sale records for the same dog. See the example:

Inv # Dog # Returned ReturnedDate ReturnedComment
ReturnedStore ReturnedSalePrice ReturnedSaleDate ReturnedInvoice
395 1227 Y 12/17/2003 Injured leg
WIZ $215.00 12/19/2003 399
399 1227

However, there are also records for dogs that have only been sold once and
they don't have any of the returned fields filled in. So I cannot query for
records where the returned fields are Not Null. I would so so appreciate
any help with this. It is driving me crazy.

Joan
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update problem 4
Invoicing problem 0
Update problem 4
Returning the correct records 4
Update query won't work 10
Query problem 0
Outer Join and a two step query 1
Subform problem 4

Top