Hi,
I need a suggestion as to which method will be most efficient way of accomplishing this task. I need to build invoice report with Garment Sizes and Quantity. There are seperate records for different sizes with their corresponding quantity in the shipment table. I have to display this information in Invoice Report in a single row with quntities under size lables ( S, M, L, XL, XXL ). Access doesnt allow grouping on multiple fields so i have to stick to programming. I was thinking of the following two methods
a. I come up with a nested SQL which does the job.
b. Before the report is displayed i go through the record set and edit the recordset to get all the sizes and quantity in one row.
c. WILDER APPROACH!! I change the query which i have posted below to exempt size and quantity information so there will be only record for Garment, Style and Colour combination and then run query for each combination for every size and their relevant quantity and then either place those values in the record set and displaying the edited record set or manually setting those values on the report. TBH i havent really thought about displaying at the moments as i havent gone through the calculating part.
But before i go down any of these approaches i want to find out if it is possible to
1. Somehow group three fields in a query
2. Which event is triggered when the report is opened just before its displayed and on form navigation
Or if some one has more experience with Access Reports as i dont have any and they can inform me of a way that i dont have to go through all this. - SELECT Invoice.InvoiceNumber, Invoice.ShipmentNumber, Shipment.ShipmentDate, Shipment.OrderNumber, ShipmentDetails.GarmentNumber, ShipmentDetails.StyleNumber, ShipmentDetails.SizeID, ShipmentDetails.ColourCode, ShipmentDetails.ShipmentQty, ShipmentDetails.UnitPrice, ShipmentDetails.Discount, ShipmentDetails.Price, StockOrder.StockOrderNumber, Customer.CustomerFullName, Customer.CustomerCompanyName, Customer.CustomerInvoiceAddress, Customer.CustomerInvoiceCity, Customer.CustomerInvoicePostcode, Customer.CustomerShippingAddress, Customer.CustomerShippingCity, Customer.CustomerShippingPostcode
-
FROM (Customer INNER JOIN StockOrder ON Customer.CustomerNumber = StockOrder.CustomerNumber) INNER JOIN ((Shipment INNER JOIN Invoice ON Shipment.ShipmentNumber = Invoice.ShipmentNumber) INNER JOIN ShipmentDetails ON Shipment.ShipmentNumber = ShipmentDetails.ShipmentNumber) ON StockOrder.StockOrderNumber = Shipment.OrderNumber
-
ORDER BY ShipmentDetails.GarmentNumber, ShipmentDetails.StyleNumber, ShipmentDetails.ColourCode;
-
10 1935
I apologize for posting in my own question but I need help as soon as possible. Could someone give me any ideas?
MSeda 159
Recognized Expert New Member
I think a crosstab query will help you. in the crosstab query set the garmet name or ID to the Row heading and Select Size as the Column heading then sum or count the Quantity as the value.
the result is a single record for each garmet displaying the quantity of each size. is this what you are trying to acheive?
I think a crosstab query will help you. in the crosstab query set the garmet name or ID to the Row heading and Select Size as the Column heading then sum or count the Quantity as the value.
the result is a single record for each garmet displaying the quantity of each size. is this what you are trying to acheive?
Thanks for the reply. I dont think it will help as the row has to be decided by Combination of GarmentNumber, StyleNumber and ColourCode. Then there is columns for each size but i dont have to count or sum, the quantities come from shipment.
I will give crosstab a try as i never tried it before but in the meanwhile i was trying this for every size it did take some time to load. I do face that there might be some logical mistake in this statement which is as follows: -
ControlSource =nz(DLookUp("ShipmentQty","ShipmentDetails","SizeID = 'S' or SizeID = '30' And ShipmentNumber = " & [ShipmentNumber] & " AND GarmentNumber = '" & [GarmentNumber] & "' AND StyleNumber = '" & [StyleNumber] & "' AND ColourCode = '" & [ColourCode] & "'"),0)
-
It is returning values where there is no value in the shipment e.g. in case of XL.Do you think OR is causing any problem?
MSeda 159
Recognized Expert New Member
I think the crosstab will work you since you can have more than one row heading. I didn't really examine the code you posted yet because I think the crosstab is much easier but if after fiddling with a cross tab you find it won't work for you we can look at some of the other things you are trying.
I think the crosstab will work you since you can have more than one row heading. I didn't really examine the code you posted yet because I think the crosstab is much easier but if after fiddling with a cross tab you find it won't work for you we can look at some of the other things you are trying.
Thanks a lot. I have tried Crosstab now. The code for the query is -
TRANSFORM Sum(ShipmentDetails.[ShipmentQty]) AS SumOfShipmentQty
-
SELECT ShipmentDetails.[GarmentNumber], ShipmentDetails.[StyleNumber], ShipmentDetails.[ColourCode]
-
FROM ShipmentDetails
-
GROUP BY ShipmentDetails.[GarmentNumber], ShipmentDetails.[StyleNumber], ShipmentDetails.[ColourCode]
-
PIVOT ShipmentDetails.[SizeID] ;
-
Now i must say the crosstab query is amazing. It came within an inch of solving my problem but only just :(
The problems i am having with it are that there are two kinds of sizes S,M,L,XL,XXL for Shirts e.t.c and 30,32,34,36,38 for Jeans etc. Now the display columns on the report are as follows S/30, M/32, L/34, XL/36, XXL/38. Cross tab will display each size seperately rather than what is required. Also the Total Quantity and Price need to be summed for all the relavant sizes and displayed seperate column as well as the unit price for each row.
MSeda 159
Recognized Expert New Member
you can either make a second query or perform the calculations directly on the report.
If the price is the same for all sizes of a Garmet/Style/Color then you can just add it to the cross tab query as a row heading. In order to combine the size catagories for S/30, M/32 etc... just add the two fields if you're doing the calculations on the report just set the control source to "= [S] + [30]" and so on. for the total of all sizes you would do the same but with all of the size fields included in the equation. On your report or in the secondary query multiple the total of all sizes by the total column to get the extended total.
Excellent stuff!! You are a star. Problem solved. One more thing came up though. If the shipment table doesnt have any quantities for a size then the size column for that size doesnt come up and i get errors. What do you recomend in this situation?
MSeda 159
Recognized Expert New Member
Use a union query to add 0 quantity entries for every size to ensure they appear in the crosstab.
SELECT ShipmentDetails .[GarmentNumber], ShipmentDetails .[StyleNumber], ShipmentDetails .[ColourCode], ShipmentDetails .[SizeID], ShipmentDetails .[ShipmentQty] FROM ShipmentDetails
Union SELECT 0, 0, 0, “S”, 0 FROM ShipmentDetails
Union SELECT 0, 0, 0, “30”, 0 FROM ShipmentDetails
Union SELECT 0, 0, 0, “M”, 0 FROM ShipmentDetails
Union SELECT 0, 0, 0, “32”, 0 FROM ShipmentDetails
Etc.
then use this query for the crosstab instead of shipmentdetails directly.
You will probably want to make a query on the crosstab query to filter out the dummy records and clean up the nulls as well as combine the sizes to be the datasource for your report, something like
SELECT CrossTab.[GarmetID], CrossTab.[StyleID], CrossTab.[Color], CrossTab.Cost, nz([S],0) + nz([30],0) AS [S/30] etc…
FROM CrossTab
WHERE CrossTab.[GarmetID] <> 0;
Cheers for that i managed to do it through format function as follows -
TRANSFORM nz(Sum(StockOrderDetails.Quantity),0) AS SumOfQuantity
-
SELECT StockOrder.StockOrderNumber, StockOrder.StockOrderDate, StockOrder.StockOrderShipDate, Customer.CustomerFullName, Agent.AgentFullName, StockOrder.SeasonNumber, StockOrderDetails.GarmentNumber, StockOrderDetails.StyleNumber, StockOrderDetails.ColourCode, Colour.ColourDescription, StockOrderDetails.StockNumber, StockOrderDetails.UnitPrice, StockOrderDetails.Discount, Customer.CustomerCompanyName, Customer.CustomerShippingAddress, Customer.CustomerShippingCity, Customer.CustomerShippingPostcode, Sum(StockOrderDetails.Quantity) AS [Total Of Quantity], Sum(StockOrderDetails.Price) AS SumOfPrice
-
FROM (Customer INNER JOIN (Agent INNER JOIN StockOrder ON Agent.AgentNumber = StockOrder.AgentNumber) ON Customer.CustomerNumber = StockOrder.CustomerNumber) INNER JOIN (StockOrderDetails INNER JOIN Colour ON StockOrderDetails.ColourCode = Colour.ColourCode) ON StockOrder.StockOrderNumber = StockOrderDetails.StockOrderNumber
-
GROUP BY StockOrder.StockOrderNumber, StockOrder.StockOrderDate, StockOrder.StockOrderShipDate, Customer.CustomerFullName, Agent.AgentFullName, StockOrder.SeasonNumber, StockOrderDetails.GarmentNumber, StockOrderDetails.StyleNumber, StockOrderDetails.ColourCode, Colour.ColourDescription, StockOrderDetails.StockNumber, StockOrderDetails.UnitPrice, StockOrderDetails.Discount, Customer.CustomerCompanyName, Customer.CustomerShippingAddress, Customer.CustomerShippingCity, Customer.CustomerShippingPostcode
-
PIVOT Format(StockOrderDetails.SizeID) In ("S","M","L","XL","XXL","30","32","34","36","38");
-
I have one more question. I have made a form through the same query but the fields are read only. After some digging around i found out that crosstab query based forms are readonly. Is there a way around that? It will save user entering same information everytime for a new size.
Much appreciated.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jason |
last post by:
The following stored procedure is taking too long (in my opinion). The
problem seems to be the SUM line. When commented out the query takes a
second or two. When included the response time climbs to minute and a
half.
Is my code that inefficient or is SUM and ABS calls just that slow?
Any suggestions to spead this up?
Thanks,
- Jason
|
by: Arild Larsen |
last post by:
Hei
I'd like to find total of one field based on the size of another field
Pipedim Length
2 2
2 3
2 2
1 3
1 4
3 6
3 1
|
by: ED |
last post by:
I am attempting to to write a query that has a numerous nested IIf
statements. The problem that I am having is that it is to long of a
query to be built in design mode and when I build it in sql mode after
a certain point it give me the error message that the expression is to
complex. Below is the sql code that I am using (this works so far,
anything added to the code will give me the to complex error message.)
SELECT .WONUM, .,...
|
by: John |
last post by:
I currently have a form and subform based on two tables; tblGoodsIn and
tblGoodsInDetail. The fields in the underlying tables do not contain any
price information. only foreign key links to product and price tables. I use
combo boxes to select parts and prices. I want the form to calculate totals
as I enter the data so I can check instantly that what I am entering into
the database tallies with the supplier Invoice. .
I tried doing...
|
by: serge |
last post by:
How can I run a single SP by asking multiple sales question either
by using the logical operator AND for all the questions; or using
the logical operator OR for all the questions. So it's always
either AND or OR but never mixed together.
We can use Northwind database for my question, it is very similar
to the structure of the problem on the database I am working on.
IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL
DROP TABLE REPORT_SELECTION
| |
by: clare at snyder.on.ca |
last post by:
I have a SQL query I need to design to select name and email addresses
for policies that are due and not renewed in a given time period. The
problem is, the database keeps the information for every renewal in
the history of the policyholder.
The information is in 2 tables, policy and customer, which share the
custid data. The polno changes with every renewal Renewals in 2004
would be D, 2005 S, and 2006 L. polexpdates for a given customer...
|
by: KiwiGenie |
last post by:
Hi..I’ve been teaching myself access through trial and error and google, but now I am completely confused and STUCK!
These are the relevant forms in my database:
frm_Recipes:
Main form for entering/viewing recipes. Recordsource is tblRecipes. Has a subform – frm_RecipesSubform
frm_RecipesSubform:
Continuous forms subform on frm_Recipes - shows the ingredients for the recipe displayed on main form.
FrmIngredients:
Used for entering and...
|
by: kang jia |
last post by:
hi
i am doing shopping online, i will let user choose their prodcut in the first page and then when they click" order" button, they will be redirected to do_addcart.php. i will insert their orders into "orders" table and retrieve them back and ask user to select their respective quantity and size. i have used Javascript validation:
the code is below:
<script language="JavaScript">
<!--
function validate_form ( )
{
|
by: monion |
last post by:
One more question.
How can I get query values assign them to a variable, but not show them?
for example size, $f4 in the following: How can I pass that value $f4 to the form handle page to submit to the database. I can clearly see how to pass $f2 with quantity stuck on the end.
Basically, I need to pass 3 things: 1) item#, 2) item#quantity and 3) size to a new database.
mysql_select_db("inventory");
$query="SELECT * FROM...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |