the SQL will look more or less like this.
Code:
TRANSFORM Count([ApptTable].[ApptID]) AS [CountofApptID] SELECT [ApptTable].[ApptDate] FROM [ApptTable] GROUP BY [ApptTable].[ApptDate] PIVOT "Count";
MSeda
Last Activity: Sep 27 '07, 04:44 PM
Joined: Sep 28 '06
Location:
-
You just need a simple crosstab query
the SQL will look more or less like this.
choose your appointment table in the query builder and then select crosstab as the query type choose The date field and set that as the row heading choose... -
I think it just the syntax, you have to treat a date the same whether its hardcoded or referenced. Try:
Code:=DSum("[Man Value]","Actual Data 2","[Sector]='IB' and [Date]> #" & [tbholddate] & "#")Leave a comment:
-
I don't know if this will help you, but I use unbound forms alot so I have a set of functions that I use to build my sql strings.
...Code:Global SQLinsert as String Global SQLvalues as String Public Function InsertInto(Source As Variant, Destination As String) As String If IsNil(Source) Then Exit Function 'IsNil is a custom function that returns true for any non value (Null, Empty, "", Nothing)
Leave a comment:
-
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...Leave a comment:
-
The searching of the data and the additions of weekly records are two separate issues, I am addressing the issue of importing the weekly additions, the query you have for your search form looks fine but is irrelevant to the issue of how to handle the importation of a weekly excel spreadsheet.
To handle the importation of the spreadsheet it is probably best to use a separate form designed specifically to assist the user in updating the DB with...Leave a comment:
-
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...Leave a comment:
-
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.Leave a comment:
-
Are you getting an error message like "the form method is unavailable?"
or just the queries aren't working? Maybe you can post the SQL from your query as well as info on the form, what type of controls are being referenced by the query etc...Leave a comment:
-
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?Leave a comment:
-
should take you to a new record. If there is something more complicated about your form that won't allow this to work post the details about your form.Code:DoCmd.GoToRecord , , acNewRec
Leave a comment:
-
Is it possible that the price contains some digits beyond the second place. Even thought the data type in the table is set to currency you can still save data with more than two decimal places it just shows only two. This could happen if the price was calculated by multiplying by a markup or dividing a pallette price to get the each and not rounding the product before saving it to the table.
You may be seeing 50.21 but are actually multiplying...Leave a comment:
-
I can't say for sure with out seeing the sql but I think you have made a common error entering the criteria. In the access query design view on the first criteria row enter the Between [Start Date] and [End Date]
and under the accountant enter Like [Enter Accountant]
move to the next criteria row and again enter the Between [Start Date] and [End Date] and enter the like [Enter Accountant] is Null in the accountant column.
If...Leave a comment:
-
Sorry for taking so long to get back.
what you want to do is have a master table that has the fields that appear in the spreadsheets you'll be importing weekly.
Then you will need to create an append query that appends the data from the temporary data you are importing to the master table.
Once you have this set up just have the user import the weekly table always saving to the temporary table and then run the query that...Leave a comment:
-
You need only one dlookup that tests for both criteria.
somthing like
Code:if not isnull(dlookup("[PK1]", "myTable", "[PK1] = " & me.PK1 & " AND [PK2] = " & me.PK2)Leave a comment:
-
Firstly, you'll want to use an option group rather than just a regular check box. An option group is a frame that contain several controls(like checkboxes) that each have a unique value a user may select only on option in a frame at a time. The frame itself is what is bound to and passes the value to the table.
That said if you want the user to be forced to move to the next question you use the afterupdate event of the frame to either close...Leave a comment:
-
if you have a backup of your database from before the corruption. delete the form from your current database and import it from the older file. see if that stops the wierdness. If that doesn't work (or you don't have a backup) try recreating the form in question from scratch.
If you haven't already compile your code when you get the offending bits removed.Leave a comment:
-
It would be best to append the new data to the existing table rather than create an new table every week. You can import the weekly batch table to a temporary table and then run an append query to add it to the main table.Leave a comment:
-
Can you post the SQL for your query?Leave a comment:
-
Add
before the first line of code. I'm not sure why you've gotten two different error messages but you need to open the form before passing the values.Code:docmd.openform "Add and Order and Details"
Leave a comment:
-
Could you post the code you tried?Leave a comment:
No activity results to display
Leave a comment: