Help Please! Database Design - Relationships

[ceinwyn]

Thank you Kryst51,
Much appreciated, but there is no need to work on the 2nd option because 1 factory sheet can only have 1 item - this is a rule.
I am going to look into the attached file, but due to my visits of the sites next week, I might not be able to give feedback.
Thanks a lot, again,
Cheers,

 

[Kryst51]

no problem, that's fantastic to hear about the 2nd option, as it cuts out a whole table and set of linkages.

 

[fredp613]

Hi, I created a similar database. In this example, you would need preset tables that all relate to one big "Master" table, you would also need a "master form", a form for each preset data table, a subform in the master table that displays the activity of the order, and command buttons on the MasterForm which users can click on and automatically opens the preset form of their choice. In your case for example you would create a table for "customers" and other preset data that you would likely want to use as search criterias in reporting in the future. Once your preset data tables are created, you can create a "Master" table. All the other tables you created before will have a relationship to this "Master" Table. I.E for your customer table, you would have the following columns: CustomerID - AutoNumber format (your primary key), CustomerName (text box format) Once you have done this, create a new column in your Master table called "CustomerID" (combo box format) under the lookup tab. Make sure to enter this SQL statement: SELECT customerID. * FROM customerID ORDER BY customerID.CustomerName). Once you have done this, go to relationships, and create a relationship from CustomerID filed in the Customer table TO CustomerID field in Mastertable. (Repeat this for all other tables of preset data.

Now your ready to create an access form that a user can enter information without messing up your database since they will have to chose options from a combo box (for each preset data). In your form, you should have command buttons that are associated to a macro. The user can click on a button called "Add/Delete Customer" and it would automatically pop up a new "customer form" and once added can save and go back to original form and the combo box is updated.

You can also create a sub form that allows 1 order that goes through multiple different phases to display and store multiple items per order.

The above is just the way that I would go about building a database with your requirements, let me know if i confused you and I can sned you a quick sample of a similar one I did.

 

[ceinwyn]

Hi fredp613,

Sorry for the late reply but I was away for a while and now struggling to get back to this database thing.

I think what you propose is something that might work for me. Is it still possible to send me a sample?
Because after all this reading and working I have realized that I need to enter the data on the go as you said but I really don't know how to do it.
Thanks a lot

 

[fredp613]

Hi, the database Is a bit too big to post on this forum, if you want I can send it to you by email (in a zip file).

 

[Scooterbug]

Once you have done this, create a new column in your Master table called "CustomerID" (combo box format) under the lookup tab.

Never, ever, in a million years is it a good idea to use a lookup at table level. It will cause you nothing but grief down the road. Do a search for Evils of Table Lookups for more information about it.

 

[fredp613]

Thanks for letting me know!

would you know anything about passing multiple selected listbox values into a query? Currently I have a listbox where the user can select multiple items and a command button that passes those selections into a query however as an IN criteria, im looking for a LIKE AND criteria, see the cmd button code below. THe SQL i would like to generate is

SELECT *
FROM Master
WHERE (((Master.ValueTypes) Like "*Aesthetic*" And (Master.ValueTypes) Like "*Existence*"));

In query design the criteria for this is: Like and('*Aesthetic*','*Existence*')


VB code i currently have that does not generate this, only an IN criteria:

Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!mslbValueTypesQry.ItemsSelected
strCriteria = strCriteria & ",'" & Me!mslbValueTypesQry.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Master " & _
"WHERE Master.ValueTypes IN(" & strCriteria & ")"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

Thank you for your help!

 

[Kryst51]

Hi Ceinwyn,

Just retook a look at what I had earlier posted, as I had forgotten all about this. I realized that between the time I posted that last database and now, I had learned where I was making a fatal error in relationships.

Here is a working one of that drafts db, still minus production tracking. but a start for you to try something maybe.

 

[ceinwyn]

Hi, I have sent my e-mail address to you in a private e-mail.
Thank you for your help.

I will give feedback when I receive it.

Cheers

 

[Kryst51]

Hi, I have sent my e-mail address to you in a private e-mail.
Thank you for your help.

I will give feedback when I receive it.

Cheers

Hi Ceinwyn, I never got a PM from you...

And good luck with your db

 

[fredp613]

Hi Ceinwyn, before I send you the database, I have to let you know that mine is research one and may not be useful to you. But I can assist you with constructing it. Before I do that however, did you ever check for access 2003 templates on the microsoft website? When I first started using access, this helped me immensily since there are templates for virtually all aspects of business (invoicing, asset tracking etc...). Here is a direct link where you can download some pre-made databases:

http://office.microsoft.com/en-ca/templates/CT101426031033.aspx


My suggestion would be to download the one that best suits your needs and modify it from there. If you've found a good one that you can build on and your having trouble let me know.

 

[Kryst51]

Hi Ceinwyn, before I send you the database, I have to let you know that mine is research one and may not be useful to you. But I can assist you with constructing it. Before I do that however, did you ever check for access 2003 templates on the microsoft website? When I first started using access, this helped me immensily since there are templates for virtually all aspects of business (invoicing, asset tracking etc...). Here is a direct link where you can download some pre-made databases:

http://office.microsoft.com/en-ca/templates/CT101426031033.aspx


My suggestion would be to download the one that best suits your needs and modify it from there. If you've found a good one that you can build on and your having trouble let me know.

Ahhhh....You sent it to him.....