SEARCH YOUR SOLUTION HERE  

Relationships query

hi..

Im struggling to get the relationships correct within my database. (4 tables)

It's for a shopping cart application

Can someone please help:

tblcustomerdetails:
custid
forename
surname
addline1
addline2
postcode
email
telno

tbllogin
customerid
username
password

tblorder
orderid
orderdate
orderqty
orderprice

tblproduct
productid
productname
productprice
productquantity
productimage

Posted On: Monday 22nd of October 2012 05:23:04 AM Total Views:  444
View Complete with Replies




Related Messages:

Tables and relationships   (167 Views)
I am trying to put this trio of tables together... I have a Parent, Child, Grand Child table structure. Class, Lesson, SubLesson Math ......Algebra ...........Algebra 1 ...........Algebra 2 This is what I have right now. Class Table with *ClassID *Class LessonTable *LessonID *Lesson Class_Lesson Table (relationship set up between ClassID and ClassID , and LessonID and LessonID) *ClassID *LessonID *Lesson This works great for the Parent and Child...I am having a hard time adding on the GrandChild Table SubLesson Table *SubLessonID *Sublesson SubLesson_Lesson Table *LessonID *SubLessonID *SubLesson 1. I then need a query that will show me all the classes, lessons and sublessons.... 2. And a query that will show me all the Lessons and Sublessons Any ideas I can send a mdf if needed.... EDIT: I can strign all these together in onw query but when I do I only get the records that exist in the SubLessons...I dont get the other records that dont have a sublesson.
More than 3 relationships between a subform and a form?   (167 Views)
I know that I am asking too much but I am having a hard time over here and I need your help again. I am making a form and substituting a subform in it. I am defining my own relationships but Access only allows me to define 3 relations but I need to define 5 relations.
How to create Family relationships?   (219 Views)
, I am building a DB for a nonprofit, and trying to figure out how to create a family relationship for contacts. Basically, it will be for mailing- so they receive only one, and to record donations. At times, John and Mary Smith will donate together, and sometimes John Smith will donate in conjuction with an organization. So, I would like the family relationship to be with John and Mary Smith, but also would like to be able to track that John Smith, but not Mary, is a member of that particular organization. I would like the donations that John & Mary make together to show up in both their records, but the donations that John makes with an organization to show only in his record and the organization record. Is this possible to create in access I'm a bit stumped at this point. Can any one help me with the best way to set this up
How do i create a subform which displays query results?   (280 Views)
hi im currently trying to complete an access project involving a music player creation. i've created a form to search through the database for specific songs (e.g by title, artiste etc.). how do i create a subform in the form so that users can type in their search in the form and see the results in the subform just below
Cannot enter data on form created from query   (234 Views)
I created a query with 2 tables. Then I used the menu to automatically create a form for the query data. It will not allow me to change any data on the form. Of course, I can change the data directly in the query results, but I need a form for another user.
Filtering recs from query ...   (177 Views)
Form layout is: Form Header -- Has detail information, i.e., Name & RoomNumber Detail Section -- 1 Row is defined. Has 3 fields form a query: RoomNumber, Asset#,Desc. plus a command button to mark it as inventoried. The three fields are defined as text with the CONTROL SOURCE pointing to the fields of the query. Section is setup to GROW. Form Footer -- Command button to exit. When I display the form, everyting comes up great. I get all 3 detail records showing in the Detail Section. What I am trying to do is apply a filter so that I only see the records for the room number displayed in the Form Header. I have applied a filter (and turned it on) during FORM LOAD, but it does not seem to work. Since it is giving me all the detail records instead of none, it appears that the filter is not being accepted at all. Or is it in the wrong place Since I need to have a command button displayed on each line, I think I need to stay with the text box process. I have tested successfully using a LIST BOX, but no ability to use the much needed command button. Any suggestions on how to properly apply the filter Thank You. Bob
Distinct records from multiple columns in SQL query   (208 Views)
I am using ASP with MS Access Database and have the created the following query for my recordset Code: SELECT galleryID, picCat, galleryName, galleryDate, picID, picName FROM tblGallery INNER JOIN tblPics ON tblPics.picCat = tblGallery.galleryID I now wish to display the recordset in a repeating table but want to remove any records returned with the same Code: galleryID I have been searching for two days and although I have found a lot of similar issues which suggest using DISTNCT, GROUP BY etc... I have not yet been able to find a solution that works. I am now running out of time for my deadline so any feedback/help would be highly appreciated Cheers Kaden712
Non-updatable query issue   (356 Views)
I've got a small DB that's been developed for materials management duties at an electronics manufacturing business. We're a contract manufacturer, who builds circuit boards of our customer's designs, using their Bill of Materials. My problem is, I need to pull a new piece of data into a form, and that requires aggregation... and I'll explain why below. (the form is built on an updatable query over a star-schema design) That aggregation (I think) is causing the entire query to become non updatable - I need a solution! About the query: It's built on a star schema, the dimension tables are used to populate drop-down selections in the form, and the only table being updated is the central fact/matrix table. The sourcing of the parts for these electronic assemblies throws an interesting twist- Essentially I have three potential part numbers for any given part that goes into these assemblies: BOM PN - the part number specified by the customer. Used PN - sometimes, purchasing has to buy an alternative part that has the same characteristics. Customer PN - Customers sometimes assign their own part number for reference, particularly when they don't require a specific vendor's part. So, my Parts table (a dimension table) contains data on parts tht we've actually purchased: Mfg PN - which ties to Used PN - parts we've actually bought, and are therefore in our inventory system. Manufacturer - The manufacturer of that part number. Description - straight off the customer's BOM. The Form: Right now it's nearly as simple as my Parts table - it's got controls for MfgPN, Manufacturer, and Quantity. To pull in the Customer PN for reference, I have to pull it from the Assembly Details table, and that means I need to basically do a "select distinct", since the corresponding Mfg PN's are in there many times. How can I include the Customer PN, so that it corresponds accurately with the existing data on the inventory management form - without making that query non-updatable
Howto Stop query from asking for value?   (197 Views)
I have a query Ive made that does some math with current values. Fields: Month, Delay Min Before, Delay Min After, Delay Min Saved, Percent: Round((([SumOfDelay Min Before]-[SumOfDelay Min After])/[SumOfDelay Min Before])*100,1) The last one is to get a percent saved from Before and After. My issus is when I run this query it will ask for "SumofDelay Min Before" and again for "SumOfDelay Min After". If I just click thru both and the query will output the correct numbers for me. From this query I made a sub from. The trouble is when I open the form for use it asks me every time for both values when the refresh button is hit. Im thinking Ill have to make a 2nd query with the last field to get this to work correctly. Sound right Edit: more info The query has "totals" clicked. Month is Group by, Before/After/Saved are all SUM, Percent is Group by.
Unequal query relationship   (182 Views)
Hi i need some help pls! I have a local table (Table A) with some data in and i have a link to a table (Table B) which is populated from sql/odbc. I set the join to include all records from A and only those from B that are equal, when I try to run this i get an error stating: "that it could not be executed as it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that in your sql statement" I have no idea how to do this, can you help pls
Select query access 2000   (296 Views)
hello there I have a problem. I have a really simple table here: table1 with two fields A and B. The content of the table is: a1, b11 a1, b12 a1, b13 a2, b21 a2, b22 a2, b23 A is primary key. My question is: I need to build a query which will get these rows a1, b11/nb12b13/n a2, b21/nb22/nb23 from the content of the table1 that I mentioned. Would you please suggest some way to build a select which would get these two rows I could think of a query which would look like something like this: select A, T1.B,"/n",T2.B from table1 as T1, table1 as T2 where T1.A = T2.A but this not only does not solve my problem but it is my closest approach to a possible solution.
How to query for query text?   (223 Views)
Hi all. This is probably a pretty easy one, I've just never done it so I'm not sure how to get at the information. I need to get my hands on some code which will query for the text of all my queries. I basically need to search through all my queries (this would include ones behind forms, but if I can just get at the text in the standalone queries, it would be a start) in order to search for a few tables/queries being used in those queries, so I can alter them to a new approach. What I want to do is spit all the query strings out into a text file or something and search through to find out which ones are utilizing the queries/tables in question, so I can start working on changing them. Is there a way to do this with the querydefs collection or with the msysqueries object
Requery Question   (222 Views)
I have a list box on a main form. When the list box is updated it should requery within per the after update procedure two subforms [sfrmAgency] and [sfrmEmployment]. The after update procedure is returning an error stating that it "can't find the field 'sfrmAgency'...", however [sfrmEmployment] works okay. The same thing happens on my OnCurrent procedure for the main form. The after update procedure includes: [Forms]![frmPerson]![sfrmEmployment].Requery [Forms]![frmPerson]![sfrmAgency].Requery
can anyone modify this query statement   (341 Views)
hi im a first time user of access, can anyone modify this query statement... select IDno, LName + ', ' + FName as Name, course, Status, (select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed = '2008/03/01' and io.DBorrowed
Displaying query results in a form   (220 Views)
Hi , I have a query which totals the amount of all invoices for a specific project number. I would like to take this total and have it displayed on the form from where overall project information is inputed. Right now I have a table which shows general project information and a table showing invoice infomation (linked to the general table via a project #) I also have two forms, one for general info and there's a button which opens the invoice form. When the invoice form opens, it opens an invoice with the corresponding project number shown on the general form. I would like the total invoice amount (from the query) displayed on the corresponding general form. ex: in the general form for project# 100, I click invoice once and enter $50, I click invoice again and enter $100. The query automatically shows a total of $150 for project# 100. I would like this $150 displayed on the general form for quick reference.
How do i pull subform data to a query?   (322 Views)
, I have a form with a subform. I have the subform running a update query on a command button in the subform. The subform has the textboxes and I cant seem to get the query to pull the data from those textboxes. I have used the normal [forms]![form_name]![textbox_name] in the past but everything I try to pull the subform textbox data is not working. I have tried [forms]![form_name]![subform_name]![textbox_name] [forms]![form_name]![subform_name].[form]![textbox_name] [forms]![form_name]![subform_name].[form].[textbox_name] Nothing works. What is the correct syntax for this
[Reports] Help with query-reports   (243 Views)
, I have made a custom form to open up when I start a query which then enters the information I enter in the form, into the query. The query then produces the results in a report. My problem is, I wanted it to display all the records when I leave a field blank. BUT when I enter a "Location/Contract", it brings up the records I want with that location/contract but it also brings up some records that don't have an entry there. Am I making sense If not, here is some screenshots: I enter "Bridge Cross Rd" And it brings up those without a location/contract... Any Ideas The code for my report is: Code: Option Compare Database Private shadeNextRow As Boolean Const shadedColor = 13356495 ' Const shadedColor = 15726583 ' alternative shade colors ' Const shadedColor = 14078404 ' Const shadedColor = 13356495 ' Const shadedColor = 14281974 Const normalColor = 16777215 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo Detail_Format_Error ' Choose a color based on the shadeNextRow value If shadeNextRow = True Then Me.Section(acDetail).BackColor = shadedColor Else Me.Section(acDetail).BackColor = normalColor End If ' Switch the color for the next row shadeNextRow = Not shadeNextRow Detail_Format_Exit: Exit Sub Detail_Format_Error: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Detail_Format_Exit End Sub Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = 1 Then txtPageSum = txtPageSum + Cost End If End Sub Private Sub PageHeaderSection_Print(Cancel As Integer, _ PrintCount As Integer) txtPageSum = 0 End Sub Private Sub Report_Close() DoCmd.Close acForm, "Master" End Sub Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no records to report", _ vbExclamation, _ "No Records" Cancel = True End Sub Private Sub Report_Open(Cancel As Integer) ' Set public variable to true to indicate that the report ' is in the Open event bInReportOpenEvent = True ' Open Sales By Category Dialog DoCmd.OpenForm "Master", , , , , acDialog ' Cancel Report if User Clicked the Cancel Button If IsLoaded("Master") = False Then Cancel = True ' Set public variable to false to indicate that the ' Open event is completed bInReportOpenEvent = False End Sub And my Form: Code: Private Sub cmdCancel_Click() DoCmd.Close End Sub Private Sub Form_Open(Cancel As Integer) If Not bInReportOpenEvent Then ' If we're not called from the report MsgBox "For use with the Reports only", _ vbOKOnly Cancel = True End If Form_Open_Exit: Exit Sub End Sub Private Sub OK_Click() Me.Visible = False End Sub And for some reason this is what my Query looks like:
Calculate Yes/No fields in a query   (500 Views)
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return If so, can you share how this could be accomplished.
Replacing query   (541 Views)
hi. i are replacing queries in my tables to get the database ready to convert to SQLServer. Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record. during creation, the record shows a date, id, task, hours. the datatable needs date, id, task_code, task_name, task_job, hours. On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". columns are needed as different areas use the same task code but use different jobs or names. I was told to use a insert into statement, however am having problems with it. have as follows: SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; " rec.Open SQLStatement, , adOpenDynamic job = rec(1) task = rec(2) rec.Close SQLStatement = " Insert into [tableb]([Job_name],[Task_name]) " & _ " values (" & job & ", " & task & " ) " rec2.Open SQLStatement, , adOpenDynamic db.Execute (SQLStatement) any point in the right direction would be greatly appreciated.
ERROR: Operation must use an updateable query.   (511 Views)
asp code: Code: error: Code: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. /site/content/register_act.asp, line 13 It's says it has something to do with permissions... I set writing & reading permissions for the db and the directory that contains it through contorl panel->Administrative Tools->Internet Information Services I have no "security tab" when I rightclick->properties the db file or it's directory even though the file system is NTFS in spite all these when I rightclick->properties the directory that contains the db I see a "read only" square marked in the general tab-attributes, when I try to unmark it seems to be unmarked but when I rightclick->properties again it's marked again... what should I do