J
Joan
Hi,
I am constructing a query for a report Pre-Invoice where the report prints
out customers and the dogss that they have bought that need invoicing. The
problem comes in with returned dogs that are later resold. I will try to
explain this as best as I can. There is a query of dog records
(EnterStoresQuery) where when the Store Code field is filled in on a dog
record when that dog has been sold and will need invoicing. Except in the
case of returned dogs. Below is the SQL for my EnterStoresQuery:
SELECT Dogs3Query.Store, Dogs3Query.Salesperson, Dogs3Query.SalesPrice,
Dogs3Query.[Short Name], Dogs3Query.Sex, 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
FROM Dogs3Query
WHERE (((Dogs3Query.DateSold) Is Null)) OR (((Dogs3Query.DateSold) Is Not
Null) AND ((Dogs3Query.Returned) Is Not Null))
ORDER BY Dogs3Query.[Short Name];
Most of the fields above come from the Dogs, Breeders, and Litters tables.
Except for DateSold which is in the Invoices table and all the field names
with the word 'Returned ' in them. They originate in the Sales table. At
the time the dog is invoiced entries are made in the Invoices table and the
Sales table. The Invoice Number, Store Code and DateSold go in the Invoices
table. So when the dogs are invoiced , the dog comes off of the
EnterStoresQuery. The entry into the Sales table is the Dog Number and the
Invoice Number. This table has a composite primary key of Invoice Number
and Dog Number and relates each dog to an invoice. When the Dog is returned
it goes back on the EnterStoresQuery.
The SQL for my Pre-Invoice report is:
SELECT Customers.CustName, Customers.Address, Customers.City, Customers.ST,
Customers.ZIP, Customers.Phone, EnterStoresQuery.[Short Name],
EnterStoresQuery.Sex, EnterStoresQuery.[Dog Number], EnterStoresQuery.Color,
EnterStoresQuery.Registry, EnterStoresQuery.Pen,
EnterStoresQuery.SalesPrice, EnterStoresQuery.BreederCode, (Date()-[Whelped
Date])\7 & "-" & (Date()-[Whelped Date]) Mod 7 AS Age,
EnterStoresQuery.[Microchip Number], EnterStoresQuery.Store,
EnterStoresQuery.Salesperson, EnterStoresQuery.Returned,
EnterStoresQuery.ReturnedStore
FROM Customers INNER JOIN (EnterStoresQuery INNER JOIN Litters ON
EnterStoresQuery.[Litter Number] = Litters.[Litter Number]) ON
Customers.CustomerID = EnterStoresQuery.Store
WHERE (((EnterStoresQuery.Returned) Is Null)) OR
(((EnterStoresQuery.Returned) Is Not Null) AND
((EnterStoresQuery.ReturnedStore) Is Not Null));
This query brings up the right dogs to invoice, except the returned dog
which has been resold is displayed as going to the first store [Store Code]
and not to the store where it was most recently sold to ([ReturnedStore]).
How do I get my query to handle this so that the Store listed for the
returned dog is the [ReturnedStore]. I have tried an IIf statement in my SQL
for my Pre-Invoice report on the CustName's field line (on the QBE design
view). However I get an error message about using a circular reference.
Here is the IIf statement I tried.: IIF([ReturnedStore] IS NULL, CustName,
{ReturnedStore])
Could someone give me some advice on how to handle this? Hope this isn't
too confusing. I will try to explain further if I am not clear.
Joan
I am constructing a query for a report Pre-Invoice where the report prints
out customers and the dogss that they have bought that need invoicing. The
problem comes in with returned dogs that are later resold. I will try to
explain this as best as I can. There is a query of dog records
(EnterStoresQuery) where when the Store Code field is filled in on a dog
record when that dog has been sold and will need invoicing. Except in the
case of returned dogs. Below is the SQL for my EnterStoresQuery:
SELECT Dogs3Query.Store, Dogs3Query.Salesperson, Dogs3Query.SalesPrice,
Dogs3Query.[Short Name], Dogs3Query.Sex, 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
FROM Dogs3Query
WHERE (((Dogs3Query.DateSold) Is Null)) OR (((Dogs3Query.DateSold) Is Not
Null) AND ((Dogs3Query.Returned) Is Not Null))
ORDER BY Dogs3Query.[Short Name];
Most of the fields above come from the Dogs, Breeders, and Litters tables.
Except for DateSold which is in the Invoices table and all the field names
with the word 'Returned ' in them. They originate in the Sales table. At
the time the dog is invoiced entries are made in the Invoices table and the
Sales table. The Invoice Number, Store Code and DateSold go in the Invoices
table. So when the dogs are invoiced , the dog comes off of the
EnterStoresQuery. The entry into the Sales table is the Dog Number and the
Invoice Number. This table has a composite primary key of Invoice Number
and Dog Number and relates each dog to an invoice. When the Dog is returned
it goes back on the EnterStoresQuery.
The SQL for my Pre-Invoice report is:
SELECT Customers.CustName, Customers.Address, Customers.City, Customers.ST,
Customers.ZIP, Customers.Phone, EnterStoresQuery.[Short Name],
EnterStoresQuery.Sex, EnterStoresQuery.[Dog Number], EnterStoresQuery.Color,
EnterStoresQuery.Registry, EnterStoresQuery.Pen,
EnterStoresQuery.SalesPrice, EnterStoresQuery.BreederCode, (Date()-[Whelped
Date])\7 & "-" & (Date()-[Whelped Date]) Mod 7 AS Age,
EnterStoresQuery.[Microchip Number], EnterStoresQuery.Store,
EnterStoresQuery.Salesperson, EnterStoresQuery.Returned,
EnterStoresQuery.ReturnedStore
FROM Customers INNER JOIN (EnterStoresQuery INNER JOIN Litters ON
EnterStoresQuery.[Litter Number] = Litters.[Litter Number]) ON
Customers.CustomerID = EnterStoresQuery.Store
WHERE (((EnterStoresQuery.Returned) Is Null)) OR
(((EnterStoresQuery.Returned) Is Not Null) AND
((EnterStoresQuery.ReturnedStore) Is Not Null));
This query brings up the right dogs to invoice, except the returned dog
which has been resold is displayed as going to the first store [Store Code]
and not to the store where it was most recently sold to ([ReturnedStore]).
How do I get my query to handle this so that the Store listed for the
returned dog is the [ReturnedStore]. I have tried an IIf statement in my SQL
for my Pre-Invoice report on the CustName's field line (on the QBE design
view). However I get an error message about using a circular reference.
Here is the IIf statement I tried.: IIF([ReturnedStore] IS NULL, CustName,
{ReturnedStore])
Could someone give me some advice on how to handle this? Hope this isn't
too confusing. I will try to explain further if I am not clear.
Joan