J
Joan
Hi,
I am not sure if my post should be to the queries newsgroup or the
formcoding newsgroup but maybe someone in this newgroup will understand what
I am trying to do.
My company deals with selling dogs, however some dogs may be returned if
they are sick. Later when they are well. they could be resold to a different
store. Following are the relevant fields in the dogs table:
Dogs Table Fields: (Not complete)
Dog Number (Primary Key)
Litter Number
Purchase Price
Store
Salesperson
SalesPrice
Sales Table Fields:
Invoice Number (Composite Primary Key)
Dog Number (Composite Primary Key)
Returned ("Y" if Dog is returned to us)
ReturnedDate (Date the dog was returned to us)
ReturnedStore (Store the dog is sold to after it has already been returned
once)
ReturnedComment (Comment field on why dog was returned)
ReturnedInvoice (Invoice Number of Invoice where dog is resold or sold
again)
ReturnedSaleDate ( Date of sale when dog is sold the second time)
ReturnedSalePrice (Sale price of dog when it is sold for the second time)
Invoice Table Fields:
Invoice Number (Primary Key)
Type ( "INV" for Invoice, "ADJ" for adjustment, "CR" for Credit, "DB" for
Debit)
Store (Store code of customer invoice is sent to)
DateSold (Date of the transaction)
Debit
Credit
DogNumber (Dog Number that any adjustments or credit transactions apply to)
OnInvoice (Invoice Number that the credit is applied to)
Comment (Comment field for the reason for the credit or adjustment)
My problem deals with handling the returned dogs and then the resale of
these dogs.
Currently I have an EnterStoresQuery (inventory) that is the record source
for a form in datasheet view where the salespersons can indicate whether
they have sold a particular dog by filling in the customer's store code in
the Store control of that dog's record. Then when the user does invoicing,
they query this EnterStoresQuery for all records that have the Store field
filled in. The SQL for the EnterStoresQuery is below:
SELECT Dogs3Query.[Short Name], Dogs3Query.Sex, Dogs3Query.Store,
Dogs3Query.Salesperson, Dogs3Query.SalesPrice, Dogs3Query.Color,
Dogs3Query.Registry, Dogs3Query.BreederCode, Dogs3Query.[Dog Number],
Dogs3Query.[Litter Number], Dogs3Query.[Purchase Price], (Date()-[Whelped
Date])\7 & "-" & (Date()-[Whelped Date]) Mod 7 AS Age, Dogs3Query.Weight,
Dogs3Query.Grade, Dogs3Query.[Microchip Number], Dogs3Query.Defect,
Dogs3Query.Hold, Dogs3Query.[Spayed/Nuetered], Dogs3Query.DateSold,
Dogs3Query.Returned, Dogs3Query.ReturnedDate, Dogs3Query.ReturnedComment,
Dogs3Query.ReturnedSaleDate, Dogs3Query.ReturnedInvoice,
Dogs3Query.ReturnedStore, Dogs3Query.ReturnedSalePrice, Dogs3Query.Pen,
Dogs3Query.SpeciesCode, Dogs3Query.[Received Date],
Dogs3Query.[Litters_Breed Code], Dogs3Query.FinalStore,
Dogs3Query.FinalSaleDate, Dogs3Query.FinalSalePrice,
Dogs3Query.DateRetBreeder
FROM Dogs3Query
WHERE (((Dogs3Query.DateSold) Is Null)) OR (((Dogs3Query.DateSold) Is Not
Null) AND ((Dogs3Query.Returned) Is Not Null) AND
((Dogs3Query.ReturnedSaleDate) Is Null))
ORDER BY Dogs3Query.[Short Name], Dogs3Query.Sex DESC;
Note: In the WHERE statement above, the record comes out of
inventory(EnterStoresQuery) when the DateSold is Not Null or when
ReturnedSaleDate is Not Null. At invoicing time, DateSold is filled in. I
haven't figured out how to fill in ReturnedSaleDate, since on my Invoice
form the dogs are automatically put in the form with no distinction as to
which dogs are being sold for the first time and which for the second time
around.
The Dogs3Query in the above SQL basically combines all fields from
Dogs2Query and [DateSold] from the Invoices table. In addition it has the
following 3 expression fields:
FinalStore: Nz([ReturnedStore],IIf([ReturnedDate] Is
Null,Dogs2Query.Store,Null))
FinalSaleDate: IIf(IsNull([ReturnedSaleDate]),IIf(IsNull([DateSold]) Or
([DateSold] Is Not Null And [ReturnedDate] Is Not
Null),CDate(1),[DateSold]),[ReturnedSaleDate])
FinalSalePrice: IIf([ReturnedSalePrice]=0,[SalesPrice],[ReturnedSalePrice])
The Dogs2Query basically has an outside join from the Dogs table to the
Sales table and includes all records from Dogs and only those from Sales
where the joined fields are equal. Combining the Dogs field with the Sales
fields.
*I am thinking that it would be easier for the salespeople to use if they do
not need to mess with the fields ReturnedSaleDate, ReturnedStore and
ReturnedSalePrice. For instance when they enter a Store code the first time
a dog is sold, they enter it in a control which then stores it in the Store
field. When the dog is returned, I want this same store control to then be
blank. When a salesperson sells the dog the second time around, they enter
a store code in this same contol, but this time it stores the code in the
ReturnedStore field. Then when they go to Invoice out this sale, the new
sale date must be somehow stored in [ReturnedSaleDate] and the new sale
price (if it is different) stored in [ReturnedSalePrice]. Can anyone tell
me how to go about accomplishing this?
Any help with this at all will be very appreciated!
Joan
I am not sure if my post should be to the queries newsgroup or the
formcoding newsgroup but maybe someone in this newgroup will understand what
I am trying to do.
My company deals with selling dogs, however some dogs may be returned if
they are sick. Later when they are well. they could be resold to a different
store. Following are the relevant fields in the dogs table:
Dogs Table Fields: (Not complete)
Dog Number (Primary Key)
Litter Number
Purchase Price
Store
Salesperson
SalesPrice
Sales Table Fields:
Invoice Number (Composite Primary Key)
Dog Number (Composite Primary Key)
Returned ("Y" if Dog is returned to us)
ReturnedDate (Date the dog was returned to us)
ReturnedStore (Store the dog is sold to after it has already been returned
once)
ReturnedComment (Comment field on why dog was returned)
ReturnedInvoice (Invoice Number of Invoice where dog is resold or sold
again)
ReturnedSaleDate ( Date of sale when dog is sold the second time)
ReturnedSalePrice (Sale price of dog when it is sold for the second time)
Invoice Table Fields:
Invoice Number (Primary Key)
Type ( "INV" for Invoice, "ADJ" for adjustment, "CR" for Credit, "DB" for
Debit)
Store (Store code of customer invoice is sent to)
DateSold (Date of the transaction)
Debit
Credit
DogNumber (Dog Number that any adjustments or credit transactions apply to)
OnInvoice (Invoice Number that the credit is applied to)
Comment (Comment field for the reason for the credit or adjustment)
My problem deals with handling the returned dogs and then the resale of
these dogs.
Currently I have an EnterStoresQuery (inventory) that is the record source
for a form in datasheet view where the salespersons can indicate whether
they have sold a particular dog by filling in the customer's store code in
the Store control of that dog's record. Then when the user does invoicing,
they query this EnterStoresQuery for all records that have the Store field
filled in. The SQL for the EnterStoresQuery is below:
SELECT Dogs3Query.[Short Name], Dogs3Query.Sex, Dogs3Query.Store,
Dogs3Query.Salesperson, Dogs3Query.SalesPrice, Dogs3Query.Color,
Dogs3Query.Registry, Dogs3Query.BreederCode, Dogs3Query.[Dog Number],
Dogs3Query.[Litter Number], Dogs3Query.[Purchase Price], (Date()-[Whelped
Date])\7 & "-" & (Date()-[Whelped Date]) Mod 7 AS Age, Dogs3Query.Weight,
Dogs3Query.Grade, Dogs3Query.[Microchip Number], Dogs3Query.Defect,
Dogs3Query.Hold, Dogs3Query.[Spayed/Nuetered], Dogs3Query.DateSold,
Dogs3Query.Returned, Dogs3Query.ReturnedDate, Dogs3Query.ReturnedComment,
Dogs3Query.ReturnedSaleDate, Dogs3Query.ReturnedInvoice,
Dogs3Query.ReturnedStore, Dogs3Query.ReturnedSalePrice, Dogs3Query.Pen,
Dogs3Query.SpeciesCode, Dogs3Query.[Received Date],
Dogs3Query.[Litters_Breed Code], Dogs3Query.FinalStore,
Dogs3Query.FinalSaleDate, Dogs3Query.FinalSalePrice,
Dogs3Query.DateRetBreeder
FROM Dogs3Query
WHERE (((Dogs3Query.DateSold) Is Null)) OR (((Dogs3Query.DateSold) Is Not
Null) AND ((Dogs3Query.Returned) Is Not Null) AND
((Dogs3Query.ReturnedSaleDate) Is Null))
ORDER BY Dogs3Query.[Short Name], Dogs3Query.Sex DESC;
Note: In the WHERE statement above, the record comes out of
inventory(EnterStoresQuery) when the DateSold is Not Null or when
ReturnedSaleDate is Not Null. At invoicing time, DateSold is filled in. I
haven't figured out how to fill in ReturnedSaleDate, since on my Invoice
form the dogs are automatically put in the form with no distinction as to
which dogs are being sold for the first time and which for the second time
around.
The Dogs3Query in the above SQL basically combines all fields from
Dogs2Query and [DateSold] from the Invoices table. In addition it has the
following 3 expression fields:
FinalStore: Nz([ReturnedStore],IIf([ReturnedDate] Is
Null,Dogs2Query.Store,Null))
FinalSaleDate: IIf(IsNull([ReturnedSaleDate]),IIf(IsNull([DateSold]) Or
([DateSold] Is Not Null And [ReturnedDate] Is Not
Null),CDate(1),[DateSold]),[ReturnedSaleDate])
FinalSalePrice: IIf([ReturnedSalePrice]=0,[SalesPrice],[ReturnedSalePrice])
The Dogs2Query basically has an outside join from the Dogs table to the
Sales table and includes all records from Dogs and only those from Sales
where the joined fields are equal. Combining the Dogs field with the Sales
fields.
*I am thinking that it would be easier for the salespeople to use if they do
not need to mess with the fields ReturnedSaleDate, ReturnedStore and
ReturnedSalePrice. For instance when they enter a Store code the first time
a dog is sold, they enter it in a control which then stores it in the Store
field. When the dog is returned, I want this same store control to then be
blank. When a salesperson sells the dog the second time around, they enter
a store code in this same contol, but this time it stores the code in the
ReturnedStore field. Then when they go to Invoice out this sale, the new
sale date must be somehow stored in [ReturnedSaleDate] and the new sale
price (if it is different) stored in [ReturnedSalePrice]. Can anyone tell
me how to go about accomplishing this?
Any help with this at all will be very appreciated!
Joan