Combine Like Product Fields In Table And Total Quantity For Those Like Fields?
Apr 22, 2015
I have a product table that includes the name of the product, the quantity ordered, and the unit price. This is a summary table for the whole year showing all of the orders. The Primary Key is [ID] from the [tblOrders] table. I attached a screen shot of my query. I don't know if you can see it. I want to generate a report where each product is listed once, the unit price for that product is listed, the total number of that item that was ordered throughout the year, and finally, the total amount spent for each item during the year.
I've now populated my database with all my sales information.
Now, towards plan/prepare 'replenishments' from suppliers, I need to know how many of each product I've sold (ideally between two given dates ....because recent sales history is more important than say sales information from 1 year ago), but let's keep it simple!
How can I get such info out of my database, the info is contained in my database something like this...
Code: Order date Qty Product ID
1-feb-2014 1 widget A 2-feb-2014 1 widget A 2-feb-2014 1 widget B 3-feb-2014 2 widget A 4-feb-2014 5 widget C
(in my database, qty is declared as 'double', where Product ID is categorized as 'text')
So I'd like an 'order summary' from the above raw data (contained in my database) to look something like this...
Code: Widget A Sales = 4 units Widget B Sales = 1 units widget C Sales = 5 units
How can I do that please? (I looked at access reports, but couldn't see a way of multiplying 'product by quantity' & then total it all up).
In access 2003 i have an external Oracle table named EXT-COM_EXTRA_DATA_FIELDS in this table I have the following fields: COM_DATA_KEY, COM_DATA_FIELD_NO, COM_DATA_VALUE
From this I have created a query named: LOOKUP-BARCODES-10 this looks at the above table by filtering the the records on the field: COM_DATA_FIELD_NO where this =10.
Also I have created a second query named: LOOKUP-BARCODES-20 this looks at the above table by filtering the records on the field: COM_DATA_FIELD_NO where this =20
The problem is i would like to combine the numbers in the COM_DATA_VALUE field for the same product (ie the same COM_DATA_KEY record) on the same rows without any duplicate records as shown below:
As you can see from above some records only have data in one COM_DATA_VALUE (like 011C/x) where COM_DATA_FIELD_NO = 10 but no data for the same product where the COM_DATA_VALUE = 20.
Im thinking maybe i can create a new join query that looks at the above two query’s and combines the values in the COM_DATA_VALUE for the same product onto the same record row but im not sure how to go about this without creating duplicates & its been some time since ive done joined queries. Any help would be great?
ive attached a cut down version of the database with the table imported from oracle rather than being linked to oracle if this helps.
I have a table called Packages, in this table I have multiple fields with just short dates example of fields (ID), (Home_1), (Home_2), (Home_3) and (Home_4). What I was wondering is there a way to comine all of the Home fields into one for the purpose of counting the number of dates for all the records. Total number of dates of all the Home fields? I am using Access 2010
I have a table "MansComps" which includes two fields, the "Index" field which contains a number, e.g "5" and the "Tabletype" field which contains a letter e.g "T". i have created a query based on this table, which combines these two fields and displays the result to a third field which I call "Components".
field: Index 1, 2, 3,....
field: Tabletype T
field: "Component" has to be like this: T00001, T00002,...
I have managed to combine the fields in my query using the expression:
Component: [Tabletype] & " " & [Index]
but the results appear without the leading zeros, like T1, T2..I have set my Index content to be a number and as a primary key and the Tabletype as a text. I also tried to change the format of Index to "00000" which works for the Index field separately but not for the Component field.Should I do something in the format of the Component field?
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
Any way of setting up a table containing the following date/time fields.
StartDate StartTime EndDate EndTime
Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.
In either scenerio, how do I subtract the two in a query for a report?
I have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria
I have been given the task of cleaning up an old db. One thing I'd like to do is combine 3 text fields into 1 new field. In the old db there was a form that included 3 memo text boxes, when the first one was full the user then started using the second and then the third. The relating Table fields are message, overflow message & overflow message extended.
I'd like to combine the fields into 1 and then design a new form to suit so that the memo text in the new db goes into 1 field only.
I have made a form to run reports on a database, however, I need to add a field where the end user can enter an account number and the report will find the relavent fields. The problem is that the account numbers are stored as four different fields within the database (add_char1, add_char5, add_char9, add_char13).
At the moment I am trying to use a combo box on the form to display all of the account numbers, but I have no idea how to do this, I can only get one field of account numbers (eg only entries from add_char1 displayed in the combo box). I have read several of the posts in these forums, but not really found a solution. If anyone knows of a way to display multiple fields as a single column (so that the combo box produces a list including add_char1 to add_char13), or have any other ideas then please let me know!
My table has three fields pickup_carrier delivery_carrier rail_carrier all the three fields share the same lookup source table.(TblCarrier) Is there a way i can combine (uniquely) all the three fields to a common combo box with one field to select?
I have two reports, "Quick Glance Report" and "Note Report". The Quick Glance Report comes from a query called (ACE/ARB Table Query) and the Note Report comes from a query called (notequery).
I want to be able to have a calculated field from the "Note Report" in the "Quick Glance Report". Am I able to show a calculated field from one report into another report and how would I do this (If I can that is)?
The lookup has a two column dropdown: FirstName and LastName. This is for the field, Sponsor, however I want to concatenate the First and Last name and have those stored in Sponsor. How do I do that?
Note: I just did it in the Lookup query.
Now a related problem is when I make a change to the table (e.g. Lookup), the change does not show in the form. Is there a way to "refresh" the form? My work around was to delete and re-add the field...I'm sure that is not the correct way to do it.
And when I added the field, the caption comes along with it bound to the field. Is there some magic keystroke to break the bond?
I was wondering if there is a way to combine sales by month for a year where it would show the product then for say January and the total sales and so on for each month.
I have an Access DB built that links to an excel spreadsheet. The spreadsheet changes so I cannot import and have to link.
Now the source of my spreadsheets is producing two spreadsheets with identical fields but different data. I need to combine the data from these two spreadsheets. Can I link them both and combine them in a query? How?
I wanted to combine 2 fields and a text in one text field on the access form.I have a field called "SentDateTime" and a field "Comment" I wanted to create it like this: MSD Message sent on 24/12/2014..I tried this formula in a unbound text field and it did not work:
I've spent about an hour searching this site for email issues but most I found we how to send. I want to take existing data and make an email address. I have a FName field and an LName field. In my email field I want FName.LName@email.com. the @email.com will always be the ending. Also where (on gotfocus) is the best place to put this code so it autopopulates after the FName and LName are entered?
Hey guys- I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')- xxx-xx-xxx Where x is an integer.
Table 2 has split the 3 sections of the code out to 3 individual fields, like this- Field1 Field2 Field3 xxx xx xxx
I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow... Thanks!
I want to combine six different memo fields into one. I found this code and it works to combine two fields so I edited to add a third and it does not do anything.
Is there a way to combine four fields into one field in the same record? I am trying to build Equipment Code field by using the abbreviated values of Category, Item, Type and Dimension fields.
Example: if Category=Storage System (SS), Item=Wire Deck (WD), Type=Flared (F) Dim=42x52 are the user selected values the corresponding equipment code field would be: SS-WD-F-42x51 This is done in order for all users of the database to build a proper code in the correct format.
What I have so far: I have the four fields and the equipment code field setup in a form, the equipment code text box has a control source of: =[Category] & "-" & [Item] & "-" & [Type] But when I select values of the four fields and the equipment code is populated I try to save the record and I get an error stating that the Equipment code field needs to be filled out. Three fields (Category, Item, and Type) are look-ups from other tables and Dimension is typed in.
I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.
i.e.
I have a
Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation..... Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation..... Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation..... Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....
-A person can be within one of these tables more than once but with all the same information. -A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.
Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers) Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?
We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT". The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
