Help with relations

Hello.
I have quite a problem with relations, imagine a table Clients and a
table Products. if i relate them one to one... i would have the table
Products with a list of products, price, quantity, date, and a yes/no
object called Buy(to return results on a query for reports) the
problem is with date...

how can i relate or create fields and make this work..the way i could
know when a certain product was bought, like keeping a history of
every purchase of every client...
I just cannot figure it out ... could anyone help..
Thanks.

0
K
4/24/2007 7:20:17 PM
access 16762 articles. 3 followers. Follow

1 Replies
389 Views

Similar Articles

[PageSpeed] 15

K,

The classic order entry scenario is shown in the sample database Northwind 
(Help, Sample Databases, Northwind Sample Database).  It consists of the 
following basic tables:

Clients (One side with Orders)
-----------------------------------------------
ClientID AutoNumber (Primary Key [PK])
ClientName Text
Address
City
State
Phone
....other client attributes

Products
-----------------------
ProductID AutoNumber (PK)
ProductName Text
UnitPrice Currency
....other product attributes

Orders (Many side with Clients, One side with OrderDetails)
----------------------------------------------------------------------
OrderID AutoNumber (PK)
ClientID Integer (Foreign Key [FK] to Clients)
OrderDate Date/Time
....other order attributes such as salesperson, payment method, etc.

OrderDetails
--------------------------
OrderDetailID  AutoNumber (PK)
OrderID  Integer (FK to Orders)
ProductID Integer (FK to Products)
Qty Integer (or Single if it's possible to buy partial units)
UnitPrice Currency
Discount Single

Note that the only fields duplicated from one table to another are PK-FK 
pairs.  The Orders table doesn't need the Client address, it needs only the 
FK ClientID to give "Access" to all the needed Client fields via a query 
linked on the PK-FK combination.  There is one exception--the UnitPrice field 
is duplicated because the OrderDetails table needs to capture the unit price 
stored in Products *at the time of the order*.  

Data entry is accomplished through a main form based on Orders and a 
continuous subform based on OrderDetails, linked by the OrderID (set in the 
LinkMasterFields and LinkChildFields properties of the subform).

A particularly convenient order review screen would show all orders of a 
given client, along with all the detail for each order, however, Access 
prevents a continuous subform from being embedded on another continuous 
subform.  One nifty workaround an MVP related to me is the following:

Base the main form on Clients to display a single customer and their 
address, phone, main contact, etc.  Embed a continuous subform based on 
Orders, linked by the ClientID.  As you move from one client record to the 
next, their Orders detail will display.  

Next, create an unbound textbox on the main form, called, e.g., txtOrderID.  
Then embed a 2nd subform on the main form based on OrderDetails.  This time, 
instead of linking to a *field* from the main form recordsource, set the 
LinkMasterFields property to the name of the unbound textbox, txtOrderID, and 
the LinkChildFields property to OrderID.  This link means that the 2nd 
subform will display the detail records from whatever order is identified in 
the unbound textbox.  All that's left is to write the OrderID to the textbox 
whenever you change focus from one order to another (using the 1st subform's 
OnCurrent event), or change to a different customer (using the main form's 
OnCurrent event):

Subform's OnCurrent event:
Me.Parent("txtOrderID") = Me![OrderID]

Main form's OnCurrent event:
Me![txtOrderID] = Me.MySubform.Form![OrderID]

It's a beautiful thing.

Sprinks

"K" wrote:

> Hello.
> I have quite a problem with relations, imagine a table Clients and a
> table Products. if i relate them one to one... i would have the table
> Products with a list of products, price, quantity, date, and a yes/no
> object called Buy(to return results on a query for reports) the
> problem is with date...
> 
> how can i relate or create fields and make this work..the way i could
> know when a certain product was bought, like keeping a history of
> every purchase of every client...
> I just cannot figure it out ... could anyone help..
> Thanks.
> 
> 
0
Utf
4/24/2007 8:18:04 PM
Reply:

Similar Artilces:

URGENT HELP
This morning when I try to open money I get the message "We're sorry, but Microsoft Money has experienced an internal error and will have to restart." I don't even get to the point where it opens the data file when I get this message. I have tried rebooting. Also I have changed nothing on my PC since last night when it was working . Any ideas? In microsoft.public.money, Fiddle wrote: >This morning when I try to open money I get the message >"We're sorry, but Microsoft Money has experienced an internal error and will >have to restart." >I do...

Please Please Help Me New To Excel
okay i'll do my best to ask this question. I have this spread sheet I've beentrying to figure out It has the number 5 in I6, then in J6 the formula =I6/10*21 This spread sheet is to show us the current number of sales we have now and based on todays date what we are on pace to hit by the end of the month(Octobers projected sales finish) So, what I want to know is why does it say 10*21. I know that the "21" stands for the number of business days in the month (21 days in October), but what does the 10 represent??? I looked at another spread sheet from last month, ...

Please Help with Formula!
Hello, I am a new user and I am having great difficulty in figuring out something. I have a dataset in Excel with first names, last names, states, cd sales total cost by state... and I am I started a new worksheet cales total sales y state. Now, in this new sheet I want have the states copied over and in the B2 cell I want to write a formula that will enable me to have the total cost from O2:O112 cells ("Total Sales") to be sumed up by each state with absolute references. How would I do this? Example I am currently using =SUMIF(Demographics!F2:F112, "AK", Demographics!O2:O...

Count Number of times Database is viewed
I have an Access 2000 Database that is read only with a Database Password. I have been asked to provide the number of times that Users are accessing the Database in a given oeriod. I have found VB code that will allow me to monitor changes to a database, using the Users Password. Is there a way I can simply collect the number of times the Database is accesed? Can you help? -- John Sorry but the forum you posted in is a Great Plains (GP) forum....try an Access forum. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks ...

Report help 10-04-07
Hi, I have a table with customers and orders. It looks something like: Date / Customer / Order 1/07 / James / Stapler 2/07 / James / Paperclips 3/07 / James / Post-Its Is there a way to create a report so that it out puts all the orders for one customer into one summary field like so? Customer / Order James / Stapler, Paperclips, Post-Its Thanks! James wrote: >I have a table with customers and orders. It looks something like: > >Date / Customer / Order >1/07 / James / Stapler >2/07 / James / Paperclips >3/07 / James / Post-Its > >Is there a way to create a r...

Getting debug assertion error when calling GetModuleFileName
I use GetModuleFileName in the following way. CString strAppPath; GetModuleFileName(AfxGetApp()->m_hInstance, strAppPath.GetBuffer(_MAX_PATH), _MAX_PATH); This is failing in my VC++ 6 dll. Why? I am using MFC in the dll already so I don't understand what triggers this problem? All I am trying to do is get the VB equivalent for App.path for my VC++ dll. Thanks Ali What exactly is the failure you are experiencing? "Ali Syed" <alijsyed@hotmail.com> wrote in message news:26c82868.0307110640.74b9c54b@posting.google.com... > I use GetModuleFileName in the following...

New user on OSX now Office wants key codes
Running a G4 with 3 users all using Microsoft Office no problems, email all works fine Need to install a new user for the machine. But all Microsoft applications now want all the key codes like a new registration. What file/folder do I need to change access privlages or something to make the new user able to use software already installed and registered on the machine please. HELP! Sorted by code removal at Admin level then reinput code when word boots up. Now works with all users ...

Help needed with message broadcast?
Hi, I have created my own class to get the system CPU usage in percent. The class has it's own thread and monitors the CPU every seconds or so. Now, my CDialog class needs to know when a certain threshold is reached. So I want my CPU class to broadcast a message to say 'CPU Threshold reached' and my CDialog to receive that event rather than checking all the time with the class if the threshold was reached. How would I go about doing that? Does the CPU class need to explicitly post a message to the CDialog class? How can I get the CDialog to listen to the messages broadcasted by...

help with lookup #2
I have a workbook made of 2 sheets. Sheet1 has a list of dates down column B (starting at row 4). Thes dates are in chronological order. Sheet2 has a list of anniversaries down Column C (starting at C1) tha are also in chronological order, with a text entry for each date in th adjacent cell in column D. I want my spreadsheet to automatically report into column D of sheet the text entries from column D of sheet2 alongside the appropriat date. In other words, Sheet1!$B$4 contains 1/Jan/2004 Sheet2!$C$1 contains 1/Jan/2004 Sheet2!$D$1 contains New Years Day I want my spreadsheet to reco...

Reworking an old database
I am working on a database that was originally designed and built in 2000 by someone who was just learning Access. One issue that I am faced with is how to handle an oversight and correct it without endangering the contents of the database. The Staff table has a primary key of a person's Initials and includes the following essential fields: FirstName, LastName, and Title. The Title field contains the individual's current position in the firm, and the table contains duplicate text values. I want to change this field to a numeric and link it to a table that contains t...

Newbie needs help writing a function
Hey all, I am trying to write a function for a Module. Here is my code: Public Function fncModelNo(OptionKey As Variant) Dim ModelNo As Variant ModelNo = Left([OptionKey], InStr([OptionKey], ".") - 1) Debug.Print ModelNo End Function When I try to run this I receive a Compile error: Argument not optional. What help tells me is that I am probably missing an argument on part of my code, unfortunately I seemed to not be able to find it. If I put just the Left(**********) as an expresion on a query, then the code works, but I am trying to place the code in ...

Help with SQL Statement for pivot table
I'm trying to put a sql statment from an access query into a pivot table query builder. I built the query in access then took the sql view and tried to put it into the excel query builder -- not working. Access put dbo_ in front of all of the table names so I took that out but it's got other problems too. Not sure how the date range should be specified and I wanted it to search a text field for the word "implant". In access I did this by specifying *implant* but sql doesn't seem to like it. Here's the statement: SELECT ApptPending.CreateDate, ApptPending....

Help! Messy-looking "pie chart"!!!
Because a few of my pie chart segments (or is that sectors?!) are very small percentages, the literals are overwriting each other. I once saw an example of a pie chart where lines miraculously appeared to separate the literals out of the way, but I can't find what setting does all this that. Any ideas? Thanks in advance! Meriel With the chart selected, choose Chart>Chart Options On the Data Labels tab, add a check mark to 'Show leader lines' Click OK Click on one of the labels, to select all the labels Click on one of the crowded labels, to select it Drag that label slightly ...

Microsoft CRM 4.0 as a help desk software
Hi All, I'd like to know if MS CRM can be use as a Help desk support website or not ? the scenario will be like: a user browse to support page hosted by CRM 4.0 and then entering the details to send to an email. after that the email is then inserted into database and based on a problem nature selection, the email is forwarded into a responsible person in my organisation. can MS CRM 4.0 does that ? Thanks url:http://www.ureader.com/gp/1265-1.aspx Yes you can use that as help desk. You need to do some development work, if you dont want to use hosted solution. else hosted need u...

Question indirectly related to Money 2004/2005
Hi all, I've got a question that is indirectly related to Money 2004 onwards. I have designed a software application for recipes that has an interface similar to Money 2004+, in the following ways: 1. UI Layout is the same (ie, Menubar, toolbar, taskpad, main panel, help pane, etc) 2. A tabbed toolbar like Money 2005 is also included 3. Color scheme for the menubar is similar to Money 2004 4. The task-based homepage is the same as in Money 2004, and uses the same color scheme, style and layout. The html and css files of Money were used as a starting/learning point. I would also like to...

SMTP Relaying Help
SMTP Relaying Help I am trying to configure an exchange server to relay email, and it seems that all I get in response is the following: Action: failed Status: 5.7.1 Diagnostic-Code: smtp;550 5.7.1 Unable to relay for user@domain.com Can anyone help me with this. what version of Exchange, and how did you do this? "NewGuy" <netlister@hotmail.com> wrote in message news:#MNTbfKYFHA.1404@TK2MSFTNGP09.phx.gbl... > SMTP Relaying Help > > > > I am trying to configure an exchange server to relay email, and it seems > that all I get in response is the fol...

Posting Help!
I open up the following: Transactions - Purchasing - Manual Payments Payment Number, Date, Vendor ID, Payment Method, Checkbook ID, Document Number and Amount have all been filled in. When I click Post, I get an error message saying "Transaction posting is not allowed." Can someone please tell me why this error is coming up and how I can fix it? Thanks your system has been setup to disallow transaction posting save the transaction to a batch and then post that batch. HS "JPS" <JPS@discussions.microsoft.com> wrote in message news:59A660AE-9077-493C-8AE9-A...

help with windows media player 11
I have Windows XP Media Center 2002 on my computer and have done all the updates that windows update will let me. I tried to download Windows Media Player 11, and I can't get it to install. It keeps telling me to download the Update Rollup 2 for Windows XP Media Center 2005, which I tried and it told me it wouldn't install it because I needed 2005 to do so. Any help would be greatly appreciated. Thank you!! On Wed, 30 Dec 2009 22:08:01 -0800, anitsirk24 <anitsirk24@discussions.microsoft.com> wrote: >I have Windows XP Media Center 2002 on my computer and h...

Scatterpoot not using X values properly -- using relative ranking
I have created a scatterplot where the X value is to be one value (risk) and the Y value is to be another (return). I've entered the X and Y value ranges properly and the y value is being plotted correctly, BUT the X value is not being plotted as the correct X value. Instead, the X value is being interpreted as the relative ranking in the list. In other words, # in list X value Y Value 1 12.2 13.7 2 10.7 11.3 etc. The graph is currently plotting the values as (1,13.7), then (2,11.3) and NOT with the c...

Help Importing Text File and Getting Right Format
Hi I have a text file that has our customers emails separated by commas. I want to get this list imported into excel with each of the email addresses appearing on a separate row in one column. I tried the import wizard and I get one of two results, all the emails in separate columns. Meaning they all show up in Row A under Column A-ZZZ, or they all show up in Row A Column A. The goal is to get each one in a separate row all under column A. Help is greatly appreciated. Thank you Brandon I'd open the text file in MSWord. Then change all the commas to a paragraph mark Look under the ...

Help Me Please!
Anyone know where I can get someone who understands the product and can help me configure it? I've had horrible luck with VARs. I'm not sure if I can help, but I have seen some of your posts and I know you are encountering many of the same issues I dealt with. I just put a 1.2 installation into production and it looks like we managed to get it configured OK. No major complaints, mainly because expectations from an MS product were so low! I'll give you a little of my time at 5PM Eastern today, if you'd like, or you can email me specific questions. No guarantees. Email me ...

Help with SQL and Excel.....
Hello friends, I am trying to send data from a store procedure to excel now and I could send the data to be formatted the same (color and bold) but then I run sp operating system displays an error in Excel.exe and do not understand why ... : o (someone has an idea that could be happening? Besides, I need to send the data and to create groups in excel, anyone have any idea how to do that? I was reviewing OLE but even I did not figure the solution to my problem .... heeeeeeeeeeeeellllp me :"( The code I use is: --EXEC ABC123_2 'C:\SUCCESS.xls' --///////////...

CAtalog merge not showing pictures
I am using Publisher 2003 and uding the catalog merge wizard to create photo directory.- My data source spreadsheet has a column that contains links to picture files. I have used the complete file names, but cannot seem to get the photos to show up. I have added .jpg to end of names - they all reside in same file with data source spreadsheet and publisher template file - Help!! Any other reasons that pictures won't print? MArtha Lots of help here http://office.microsoft.com/en-us/assistance/HP010381431033.aspx Create a data source for a mail merge or a catalog merge -- Mary Sauer...

Cant get help elsewhere
Hi I apologise in advance if this is not the place to put this. I have tried to get on to hotmail support and cant. For some reason hotmail, opening emails or even trying to get onto the forum is either slow (i.e. opening hotmail) or does not load at all, opening emails..does not open, or trying to get onto the forum, does not dl. Is something wrong with hotmail. other sites seem to open ok? Thanks for any help. Mel HOW TO Optimize IE for Windows Live Hotmail & windowslivehelp.com http://windowslivehelp.com/solution.aspx?solutionid=3c7c3338-369e-4220-b29d-71ce660fe...

Need help with a script to copy a file to a specific home dir
I would like to use vbscript to move a text file from a share directory to specific users home directory whereby the script pulls from a list of names (text file) and uses the users homedir path to copy the file. -- bforrest "bforrest" <guest@unknown-email.com> said this in news item news:2fc01a8a5ba07582f3c2883053076693@nntp-gateway.com... > > I would like to use vbscript to move a text file from a share directory > to specific users home directory whereby the script pulls from a list of > names (text file) and uses the users homedir path to ...