How To Build A Table Based On Another, Using Modulus?
Sep 7, 2006
Hello!
I have a table, with quantities in a field that I need to split in batches of 25, to build a new table. Example:
object price qty
blah1 1.00 144
blah2 2.00 76
My results need to look like this:
object price qty
blah1 25.00 25
blah1 25.00 25
blah1 25.00 25
blah1 25.00 25
blah1 25.00 25
blah1 19.00 19 (5x25+19=144)
blah2 50.00 25
blah2 50.00 25
blah2 50.00 25
blah2 2.00 1 (3x25+1=76)
I've been able to build a new table based on these values of 25 etc using MOD, but it's making columns. How can I build a new table and make new RECORDS using MODULUS (or another?) technique instead?? I hope this makes sense, and I hope someone can help.
Cheers
View Replies
ADVERTISEMENT
May 16, 2012
I have a xml schema which defines all things necessary (tables, field names, relationships, etc) to build a database in access. The question is whether Access could decode xml schema and build the database (create all tables, fields and relationships) automatically by macro or something else. I have tried to load xml schema by using external data in Access. However Access can only recognize some tables, not everything.
View 2 Replies
View Related
Dec 4, 2006
Can anybody help me build the last table in my database? I have taught myself Access – and can usually get by with a little help from this forum. I have been trying for days to try and solve this !!!
The database is designed to manage feedback questionnaires from attendees of training courses.
I have attached the relationship diagram.
- A course may run for several days. The details of the course are held in the table ‘Courses’. During a course, attendees may be asked to fill in several Questionnaires
- An Attendee is a Person that attends a Course. Attendees are held in the table ‘Attendees’ and their details are held in the table ‘Person’.
- There is a ‘Library’ of questions that sit in the table ‘Questions’
- The questions can be assembled into “Standard Questionnaires”. These sit in the table called “Questdesign” which is a junction table. Assembling a Standard Questionnaire involves using a form to pick questions and give them an order (Qnumber). I HAVE DONE THIS.
- A specific “Questionnaire” is a “Standard Questionnaire” that is used on a specific course. The list of Questionnaires is held in the table “Questionnaire”
- Scoresheet is the junction table between Attendee and Questionnaire (an Attendee on a course may fill in several Questionnaires and a Questionnaire may be filled in by lots of Attendees.)
I now need a table (Scores) in which to put all the scores to the questions (in other words, the scores of the questions on the Questionnaires that are completed by the Attendees on a Course.)
At the end of the day I am aiming to have a form in which:
The Attendee number is entered and all the person’s details are filled in automatically) – I HAVE DONE THIS
The Questionnaire number is entered and all the course details are filled in automatically – I HAVE DONE THIS
A Scoresheet ID is filled in (Autonumber) - I HAVE DONE THIS
A subform appears which shows the right set of questions and question numbers for the appropriate Questionnaire – with a blank column so that the user can fill in the scores which get stored in the Scores Table. - I CAN'T DO THIS !!!
I cannot work out where to link my Scores table and how to build a query that drives the correct behaviour of the subform
I hope I have explained this clearly. I would be so grateful for any help. This Forum is fabulous.
Many thanks
NoVoiceLeft
View 6 Replies
View Related
Apr 22, 2006
hi
hi
i have sql query in a string
strQuery="...."
how to create temporary table with the result of this query?
thanks
View 1 Replies
View Related
Mar 28, 2006
Using Access 2000 is it possible to specify a starting location ie (c:) and using VBA search every sub folder from this point for files ending .mdb or .xls?
I want to be able to build a table with an record showing the file name and directory for each file found.
Any help most appreciated.
Regards,
Dalien51
View 2 Replies
View Related
Apr 7, 2006
Access 2000: How can I populate a two field table (1. Table Name 2. Field Name) with the name of ever field within every table in my database using VBA code?
Regards,
Dalien51
View 2 Replies
View Related
Feb 7, 2007
I have been building my DB and learning everything from scratch. If anybody could give me any advice as to where I may be going astray, it would be much appreciated as I feel like I've hit a brick wall. I run a building firm and need a DB to look after customer information, write estimates, invoices and ultimately do the accounts.
I think my DB needs are very basic to start with and I'm guessing the system only needs to be fairly simple and straightforward. So far my DB has:-
•Customer form with an in-built Estimate subform - this form gives me a brief outline of the customer and how many estimates I have for him.
•If you then double click on an estimate number (in the Estimate subform) this takes you to my Items form (when I create an estimate normally in Word, I have to break jobs down into their components and price each item individually, hence I made an Items table). Each item has an Item Cost.
•I then run the report “Estimate” and have set up a total calculation box on the report ready to print it out for the customer.
My problem is that I would like to know how I can get the Estimate Total to appear in my Estimates Table and Estimates Subform. So far I have achieved making a crosstab query to calculate the Estimate Total (Amount) for each specific Estimate ID, but I cannot get the Total from the Crosstab Query into the Estimate Table, from which I would probably be able to get it into the Estimate Subform. I have a suspicion I need to build an expression in the Estimate Subform - but.....
Once I am over this problem I will want to turn Estimates into Invoices and have that data displayed in my Estimate Subform too, although I think I have got a good idea of how to do this, I would again be open to comments/advice if offered.
Thanks Steve
View 1 Replies
View Related
Jan 2, 2015
I am attempting to build a table relationship, however when I enforce referential integrity, I keep receiving the message: "No unique index found for the referenced field of the primary table".
I checked and I have no duplicates in the primary table or secondary table.I will set up the two tables as thus: 1. Master table - People Who Eat 2. Secondary table - a table for all people who eat pizza.
I am trying to link my pizza table to the Poeple Who Eat table. I have created my own primary key for each table (an account number). My secondary table is much smaller in population than the People Who Eat table (which would include data from other subgroups). I am trying to get the 'yes'/'no'/'uncertain' from the secondary table into the primary table without manual entry.
I have searched all over the web and have not been able to find why the tables will not link. I am new to Access, so there may be something I am overlooking. Is there a way to get this information from the other table other than building a relationship?
View 3 Replies
View Related
Nov 22, 2013
Im trying to sum a value within subform able before I even start I get ####Error as I have put in the control source the rule to create the sum
Code:
=Sum([TempTable].[hours])
I then have a function when another value is added it requires the text box which holds the sum value.
View 1 Replies
View Related
Aug 8, 2006
How do I build a form which will pull only certain work orders from my table [Work Orders], filter it through a query, [Payroll] which gives me only those work orders which match a certain repID#, for only certain Invoice Dates, and and then display a talley of the total number of times a certain product appears in the field [ProductSold] on the query [Payroll]?
View 1 Replies
View Related
Feb 23, 2007
I can't find anything in the archive relating to this...
Can someone tell me how to enable the "Build" feature in the Visual Basic screen in 2003, please?
I recently upgraded from 97 and can only find the "Build" feature in Query Design.
In 97 it was available by right clicking the screen and selecting "Build". I have no such option when right clicking in 2003.
Thanks
View 5 Replies
View Related
Aug 28, 2007
I have some database with columns 'Number', 'Mfr Name' and 'Mfr Part Number'.
Number - internal catalogue item of a company.
Mfr Name - name of a manufacturer where from we purchase the item
Mfr Part Number - part number of a manufacturer where from we purchase the item
Each item may have one or more Mfr Name & Mfr Part Numbers.
----------------------------------------------------------------------
I need to build a following query:
Input gets part of Mfr Part Number and output is all Mfr Name & Mfr Part Number of match item.
View 6 Replies
View Related
May 26, 2006
I have attacked quite a few guides ( Dummies & Absolute Beginners ) on how to build a database of my gardening clients, but have run into a problem that they can't seem to resolve. Is there anyone out there who can help me with "referential integrity" and trying to link relationships between the various tables I have created.
View 3 Replies
View Related
Jan 8, 2007
Hello All
Does anyone know how you might build sliding bar displays as an input device for scores into an Access database? I am building an application that could really use something like this.
Here is an example. First go to the 'Chelsea tab', slide the bars, submit the results, and see the results on the "Results" tab
http://news.bbc.co.uk/sport1/hi/football/teams/c/chelsea/6173703.stm
Very impressive
NoVoice
View 6 Replies
View Related
Oct 7, 2005
How can I build a date in a query that is used for filtering purposes? The data is in the records in two fields - Month and Year. In our query we have an expression that builds a date - DateRange: [Month]/1/[Year] in order to qualify the record with the criteria Between FromDate and ThruDate. DateRange as it is shown here returns a date of 12/30/1899 for all records. DateRange: #[Month]/1/[Year]# is invalid syntax. DateRange: [Month] & "/" & 1 & "/" & [Year] is not recognized as a date.
View 4 Replies
View Related
Jul 5, 2006
Hi all, I have a field that stores software versions as text. The version numbers can be simple (4.0, 5.0) or unknown or more complicated (4.5.200512, etc). The user wants to be able view data in a pivotChart without seeing interim versions- so 4.5.200512 would not show up but its data would be counted as part of 4.5. Anybody know how I can do this?
thanx
View 1 Replies
View Related
Oct 26, 2006
Hi everybody,
Let's say that I've got the following tables:
- tblCustomers(IDCust,NameCust)
- tblEmployees(IDEmpl,NameEmpl)
- tblProjects(IDProj,IDCust,IDEmpl,Duration)
My query:
SELECT tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl, Sum(tblProjects.Duration) AS SumOfDuration
FROM (tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust
GROUP BY tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl
ORDER BY tblCustomers.NameCust;
What I'd like get when running my query is:
NameCust ------ IDProj - Duration ----- NameEmpl ------- ?????????
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 7
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 9
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 9
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 17
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 4
CUST2 --------- 10 ------ 5 ------------ EMPL3 --------- 17
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 4
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 27
CUST3 --------- 14 ------ 2 ------------ EMPL1 --------- 14
CUST3 --------- 15 ------ 2 ------------ EMPL4 --------- 27
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 14
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2
where ???????? (manually done) = the sum of the Employee duration for ONE Customer !!!
But what I get instead:
NameCust ------ IDProj - Duration ----- NameEmpl ------- SumOfDuration
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 3
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 8
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 1
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 12
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 3
CUST2 --------- 10 ------ 5 ------------ EMPL3 ---------- 5
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 1
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 25
CUST3 --------- 14 ------ 2 ------------ EMPL1 ---------- 2
CUST3 --------- 15 ------ 2 ------------ EMPL4 ---------- 2
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 12
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2
Any suggestion to build this Query ??
Thanks in advance
View 3 Replies
View Related
Feb 9, 2006
Hi all.
I have been given a simple task, but i have no idea how to do it! I need a form to do the following:
User types in a reference, for example TEST1
We have a standard link along the lines of http://www.testtesttest.com/blaablaa/
We need access to append the "TEST/1" (or whatever the user has entered) to the end of the link, so the result would be:
http://www.testtesttest.com/blaablaa/TEST1
I would be extremely greateful of someone could let me know the best way of getting this done.
Kind Regards
Darren
View 1 Replies
View Related
Dec 25, 2006
Hi
I 'm new at this Forum and also I'm new programmer in Access.
I 'm trying to answer project called (Cerulean Bee) ,which is attachment down
I do the table and forms but I don't know how I can let them as it the question (Artwork Order-Employee Work Log-Print Order-Project Cost Analysis).
Her my file http://www.megaupload.com/?d=XP34TRO3
Please help me to find ant error I do , and to get best answer for my question.
I'm realy need your help
Thank you all
View 2 Replies
View Related
Feb 11, 2006
(Dear Admin, don't be mad I posted this post elsewhere but had no luck with answers)
Please guys, I just forgot how to do this:
I have a table with primary key (id) and sort of secondary key (id1). Several rows can have same id1.
id id1
1 1
2 1
3 1
4 1
5 3
6 3
7 4
8 4
9 4
...
I need to count the id1 - how many different id1 values are there.
This doesn't work:
SELECT COUNT(id1) FROM table GROUP BY id1
this also doesn't work:
SELECT COUNT(id1) FROM table
As I recall, it was something about DISTINCT function or something like that...?
Please help, I'm sure it's totally simple, but I'm stuck.
View 4 Replies
View Related
Mar 21, 2006
select student from tbl_TableName where max(date_borrowed) <= dateadd("d",-730,Date())
group by student
??? How do I set this up? and do the max date_borrowed?
Thanks.
View 2 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Mar 17, 2006
Hi, I'm new to Access.
I'm trying to do a very simple thing but I can't.
Let's say I sell a lot of cars, every day so I need a database only to collect info about selling. I need a table (SoldCars) which need refers (read: field) to 2 other tables: 1.Constructor and 2.Model, or only to Model (because Construvctor could be redundant). Anyway, when I register a new sold car, I want a menu in which I can find a Model but not after having chosne the Constructor, so when I choose "Ferrari" then I can only find "Testarossa" and "GTO" and choose one of them. Stored field can be Model, because Constructor is implicit. But I need this tip because let's say I sell thousands of cars per day and I want it to be very quick. Please help or I'll die under piles of cars!
Thanks!!!!
View 2 Replies
View Related
Aug 9, 2007
I've tried to build a second Drop menu, copying the code from the first. When i run it im getting an error 91 (i didnt understand much of what google said on it) the code is as follows:
I tried changing it to btnInput1, but that hasnt helped :( the menu gets built, but the button does not.
Sub BuildAdminMenu()
Dim mnuInput As CommandBarPopup
Dim btnInput As CommandBarButton
Dim btnInput1 As CommandBarButton
Set mnuInput = Application.CommandBars("Menu Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)
mnuInput.Caption = "&Options (beta)"
mnuInput.BeginGroup = True
mnuInput.Visible = True
mnuInput.Tag = "Build XML Files Menu"
Set btnInput = mnuInput.Controls.Add(Type:=msoControlButton, Before:=1)
btnInput.FaceId = 2761
btnInput.OnAction = "openForm"
btnInput.Caption = "New PR/IEWO"
btnInput.Style = msoButtonIconAndCaption
Set btnInput = mnuInput.Controls.Add(Type:=msoControlButton, Before:=1)
btnInput.FaceId = 2761
btnInput.OnAction = "openSearch"
btnInput.Caption = "Search Entries"
btnInput.Style = msoButtonIconAndCaption
Set mnuInput = Application.CommandBars("Menu Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)
mnuInput.Caption = "&AdminOptions (beta)"
mnuInput.BeginGroup = True
mnuInput.Visible = True
mnuInput.Tag = "Build XML Files Menu"
Set mnuInput = Nothing
Set btnInput1 = mnuInput.Controls.Add(Type:=msoControlButton, Before:=1)
btnInput1.FaceId = 2761
btnInput1.OnAction = "openAddUser"
btnInput1.Caption = "Add User"
btnInput1.Style = msoButtonIconAndCaption
View 1 Replies
View Related
Feb 20, 2008
As example, I have a table with an Item number, introduction year and a number of historical and future Sales periods set per year, these sales columns are listed Y1990, Y1991, Y1992....... Y2015.
Based on each items introduction year, I want to list the first 5 years of sales.
I wanted to create a dimensional fieldname eg: FirstYear: "Y"&[introduction year] to get the value of that respective year. (I currently just get a text saying "Y1995", and not the content )
Any help is appreciated!:)
(Note: I can't transpose the data in the tables for other reason)
View 3 Replies
View Related
Jul 19, 2014
I am relatively new to VBA and have this loop (see below) was wondering if it was possible to build it into a variable, so I could call upon it within an IF statement.
Set ctl = Me.listSeeAllAssets
For Each varItem In ctl.ItemsSelected
rs1.AddNew
rs1!AssetNo = ctl.ItemData(varItem)
rs1!DateOfService = Me.DateOfService
[Code] .....
View 7 Replies
View Related
