If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Newbie? Do I use Report or Query
I would not use autonumber PK as anything other than PK.
It would never be visible except for design purposes. Having said that, I have used Employee ID numbers as the PK in an Employees table, but I would probably stay away from that were I to do it over. I would be inclined to use for the job number a number that increments by one (or ten or whatever you want) over the previous job number. If autonumber is used, starting a record then deleting it (to use one example) would result in a gap in the job numbers. Later it might be difficult to know why the gap is there. Was the record lost? What happened? Why is there no record of it? Short answer to question 2: You can, but you really don't want to. -----Original Message----- Thank you for your suggestions, they all make sense. Just two questions 1. Same question as the next post. Why have three PK fields in JobDetails. 2. I have read conflicting opinions on this. Is it ok to use the table autonumber as both table ID/PK and as an identification number for that record outside the database. For example would using JobNumberID as the jobcard number cause any problems. "Bruce" wrote in message ... I really like the way you are explaining things in this thread, but I am puzzled by something. I'm not sure I follow why Job Details would be a field in the Job Number table. Having the PK from Job Number be the FK for job details would accomplish what is needed, I would think. Or am I on a different page? -----Original Message----- 1. As a suggestion, end your primary key fields with "ID" - this easily identifies a field name as a key. Makes reading your database easier! 2. As a suggestion, name your foreign keys the same as the corresponding primary key - identifies a field as a foreign key. Makes reading your database easier! 3. In Table Jobcard, change the field named "Date" to a different name - "Date" is a reserved word in Access 4. Assuming a supplier provides more than one product, you need a suppliers table and then refer to the supplier in Table Products by SupplierID 5. Combine your PaymentsIn and PaymentsOut tables into one table named TblTransactions and identify the type of transaction with a field called TransactionType which would have the value "In" or "Out". You can then easily get a balance by just adding up all the values in the table. 6. You need a foreign key, "JobnumberID", in Table Job Details that relates that table to Table Jobcard. 7. What is "Description" in Table Job Details? If it is for the product - you don't need this field. I suggest the following tables: Table Jobcard JobNumberID PK(autonumber) JobDate CustomerID VehicleMake VehicleRegistration JobDetails JobCode TimeOnJob Table JobDetails JobDetailID JobNumberID ProductID Description Quantity Table Products PK ProductID ProductCode (suppliers part number) ProductName SupplierID UnitPrice UnitsInStock -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "John Egan" wrote in message ... Thanks for the information, I'm gradually getting a picture of how things work after setting up the tables. The tables are setup as follows Table Jobcard JobNumber PK(autonumber) Date CustomerCode VehicleMake VehicleRegistration JobDetails JobCode TimeOnJob Table JobDetails JobNumber (Dual PK for JobNumber and ProductCode) ProductCode Description Quantity Table Products PK ProductCode (suppliers part number) ProductName Supplier UnitPrice UnitsInStock Relationships are JobNumber-JobNumber and ProductCode- ProductCode. I also have tables for Customers, Suppliers, JobCode, PaymentsIn and PaymentsOut. Any constructive critism or suggestions for improvement greatly appreciated. Regards John "PC Datasheet" wrote in message news:_KDDc.30460 ... what I really want is a conceptual model of how forms, queries and reports work together. The ONLY place data is stored in a database is in tables. Queries are used to retrieve the data you want from the table(s). Queries DO NOT store data. Forms are used to enter data into tables and to display the data you want on the screen. Forms DO NOT store data. Reports are the counterpart of Forms. Reports are used to give you a hardcopy of the data you want. Reports DO NOT store data. You CAN NOT use reports to enter data into tables. Both forms and reports can be directly based on a table or a query. If based on a query, the query can include one table, multiple tables, one other query, multiple other queries or a combination of table and queries. When a query includes multiple objects, these objects 99.99% of the time must be joined together on common fields; thus the need for primary and foreign keys. If I open a query in datasheet view, it displays like a table This is true! The datasheetview is only for you as the database designer to be able to review the data the query returns to be sure it is what you want. A query SHOULD NEVER be used in the final database to display data. As stated above, a form or report should be used to display data. As Larry said, forms and reports are usually based on a query. I'm not quite sure what you mean. I have already setup a form to enter data into the jobcard and jobdetails tables. If you're not sure, you probably don't have the correct forms and it is highly likely that the design of your tables are incorrect. I suggest you post your Product, JobCard and JobDetails tables showing the fields in each for further comment. Before we deal with your Invoice, let's be sure your tables are correct. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "John Egan" wrote in message ... Thanks Traci I have a productID field in the jobdetails table. I have spent some time designing the tables and relationships and I'm fairly happy with them now. I've looked at the Northwind database and I suppose what I really want is a conceptual model of how forms, queries and reports work together. If I open a query in datasheet view, it displays like a table. Does this mean that a query is as good as a table for data storage. For data entry you need a main form based on a query based on your jobcard table and a subform based on a query based on your jobdetails table I'm not quite sure what you mean. I have already setup a form to enter data into the jobcard and jobdetails tables. This is the first requirement, to collect the job data. At a later date I will need to invoice for these jobs. In some instances a single invoice will have details from two or more jobs. My plan was to setup a query or report to collect and organize the data from jobcard and jobdetails and Product tables and calculate extended price, subtotal, tax etc. From what you say below. Is the query entering the data into the form, or would I be using the form to retrieve data from the query. Should I scrap the existing forms and start over. Any help greatly appreciated Regards John "Traci" wrote in message news:yvADc.30219 ... John, From your description, you should have a ProductID field in your jobdetails table. For data entry you need a main form based on a query based on your jobcard table and a subform based on a query based on your jobdetails table. The reason for the two queries is first to put the records in both the main form and subform in some sort order. A second reason for the query in the subform is to allow you to create calculated fields in the query. ExtendedPrice would be a calculated field in the query. In the subform you would use a combobox based on your Products table to enter the products. Use a report and a subreport to create invoices. The report would be base on a query that pulls data from appropriate tables. The subreport would also be based on a query that pulls the data for each line item on the invoice. It would be very similar to the data entry subform. You can find excellent example on how to do all this in the NorthWinds sample database that came with your MS Office. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "John Egan" wrote in message ... I have set up a jobcard table and jobdetails table which are linked as one to many. The info for time, parts, quantity etc is entered in these tables. Two questions. 1. I want to collect this data along with prices from a products table and put it together to print and store as an invoice. What is the best method, Report or Query or a combination of both. 2. If I manage this, is the info in the Report or Query, such as extended price, subtotal, total etc, or any other info that I don't want on the jobcard ok for storage purposes. Should I set up the Report/Query to write this data to a separate table. . . |
#12
|
|||
|
|||
Newbie? Do I use Report or Query
I would not use autonumber PK as anything other than PK.
It would never be visible except for design purposes. Having said that, I have used Employee ID numbers as the PK in an Employees table, but I would probably stay away from that were I to do it over. I would be inclined to use for the job number a number that increments by one (or ten or whatever you want) over the previous job number. If autonumber is used, starting a record then deleting it (to use one example) would result in a gap in the job numbers. Later it might be difficult to know why the gap is there. Was the record lost? What happened? Why is there no record of it? Short answer to question 2: You can, but you really don't want to. -----Original Message----- Thank you for your suggestions, they all make sense. Just two questions 1. Same question as the next post. Why have three PK fields in JobDetails. 2. I have read conflicting opinions on this. Is it ok to use the table autonumber as both table ID/PK and as an identification number for that record outside the database. For example would using JobNumberID as the jobcard number cause any problems. "Bruce" wrote in message ... I really like the way you are explaining things in this thread, but I am puzzled by something. I'm not sure I follow why Job Details would be a field in the Job Number table. Having the PK from Job Number be the FK for job details would accomplish what is needed, I would think. Or am I on a different page? -----Original Message----- 1. As a suggestion, end your primary key fields with "ID" - this easily identifies a field name as a key. Makes reading your database easier! 2. As a suggestion, name your foreign keys the same as the corresponding primary key - identifies a field as a foreign key. Makes reading your database easier! 3. In Table Jobcard, change the field named "Date" to a different name - "Date" is a reserved word in Access 4. Assuming a supplier provides more than one product, you need a suppliers table and then refer to the supplier in Table Products by SupplierID 5. Combine your PaymentsIn and PaymentsOut tables into one table named TblTransactions and identify the type of transaction with a field called TransactionType which would have the value "In" or "Out". You can then easily get a balance by just adding up all the values in the table. 6. You need a foreign key, "JobnumberID", in Table Job Details that relates that table to Table Jobcard. 7. What is "Description" in Table Job Details? If it is for the product - you don't need this field. I suggest the following tables: Table Jobcard JobNumberID PK(autonumber) JobDate CustomerID VehicleMake VehicleRegistration JobDetails JobCode TimeOnJob Table JobDetails JobDetailID JobNumberID ProductID Description Quantity Table Products PK ProductID ProductCode (suppliers part number) ProductName SupplierID UnitPrice UnitsInStock -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "John Egan" wrote in message ... Thanks for the information, I'm gradually getting a picture of how things work after setting up the tables. The tables are setup as follows Table Jobcard JobNumber PK(autonumber) Date CustomerCode VehicleMake VehicleRegistration JobDetails JobCode TimeOnJob Table JobDetails JobNumber (Dual PK for JobNumber and ProductCode) ProductCode Description Quantity Table Products PK ProductCode (suppliers part number) ProductName Supplier UnitPrice UnitsInStock Relationships are JobNumber-JobNumber and ProductCode- ProductCode. I also have tables for Customers, Suppliers, JobCode, PaymentsIn and PaymentsOut. Any constructive critism or suggestions for improvement greatly appreciated. Regards John "PC Datasheet" wrote in message news:_KDDc.30460 ... what I really want is a conceptual model of how forms, queries and reports work together. The ONLY place data is stored in a database is in tables. Queries are used to retrieve the data you want from the table(s). Queries DO NOT store data. Forms are used to enter data into tables and to display the data you want on the screen. Forms DO NOT store data. Reports are the counterpart of Forms. Reports are used to give you a hardcopy of the data you want. Reports DO NOT store data. You CAN NOT use reports to enter data into tables. Both forms and reports can be directly based on a table or a query. If based on a query, the query can include one table, multiple tables, one other query, multiple other queries or a combination of table and queries. When a query includes multiple objects, these objects 99.99% of the time must be joined together on common fields; thus the need for primary and foreign keys. If I open a query in datasheet view, it displays like a table This is true! The datasheetview is only for you as the database designer to be able to review the data the query returns to be sure it is what you want. A query SHOULD NEVER be used in the final database to display data. As stated above, a form or report should be used to display data. As Larry said, forms and reports are usually based on a query. I'm not quite sure what you mean. I have already setup a form to enter data into the jobcard and jobdetails tables. If you're not sure, you probably don't have the correct forms and it is highly likely that the design of your tables are incorrect. I suggest you post your Product, JobCard and JobDetails tables showing the fields in each for further comment. Before we deal with your Invoice, let's be sure your tables are correct. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "John Egan" wrote in message ... Thanks Traci I have a productID field in the jobdetails table. I have spent some time designing the tables and relationships and I'm fairly happy with them now. I've looked at the Northwind database and I suppose what I really want is a conceptual model of how forms, queries and reports work together. If I open a query in datasheet view, it displays like a table. Does this mean that a query is as good as a table for data storage. For data entry you need a main form based on a query based on your jobcard table and a subform based on a query based on your jobdetails table I'm not quite sure what you mean. I have already setup a form to enter data into the jobcard and jobdetails tables. This is the first requirement, to collect the job data. At a later date I will need to invoice for these jobs. In some instances a single invoice will have details from two or more jobs. My plan was to setup a query or report to collect and organize the data from jobcard and jobdetails and Product tables and calculate extended price, subtotal, tax etc. From what you say below. Is the query entering the data into the form, or would I be using the form to retrieve data from the query. Should I scrap the existing forms and start over. Any help greatly appreciated Regards John "Traci" wrote in message news:yvADc.30219 ... John, From your description, you should have a ProductID field in your jobdetails table. For data entry you need a main form based on a query based on your jobcard table and a subform based on a query based on your jobdetails table. The reason for the two queries is first to put the records in both the main form and subform in some sort order. A second reason for the query in the subform is to allow you to create calculated fields in the query. ExtendedPrice would be a calculated field in the query. In the subform you would use a combobox based on your Products table to enter the products. Use a report and a subreport to create invoices. The report would be base on a query that pulls data from appropriate tables. The subreport would also be based on a query that pulls the data for each line item on the invoice. It would be very similar to the data entry subform. You can find excellent example on how to do all this in the NorthWinds sample database that came with your MS Office. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "John Egan" wrote in message ... I have set up a jobcard table and jobdetails table which are linked as one to many. The info for time, parts, quantity etc is entered in these tables. Two questions. 1. I want to collect this data along with prices from a products table and put it together to print and store as an invoice. What is the best method, Report or Query or a combination of both. 2. If I manage this, is the info in the Report or Query, such as extended price, subtotal, total etc, or any other info that I don't want on the jobcard ok for storage purposes. Should I set up the Report/Query to write this data to a separate table. . . |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query Form: Print Report | Dennis | Running & Setting Up Queries | 1 | June 6th, 2004 01:08 PM |
Report Populated by a crosstab query | Michael Noblet | Setting Up & Running Reports | 3 | June 1st, 2004 07:11 PM |
Newbie: Using Left in query for report? | Ed | Running & Setting Up Queries | 3 | May 26th, 2004 11:26 PM |
Error running query for report...but query works fine when opened separately | John | Setting Up & Running Reports | 1 | May 21st, 2004 04:15 AM |
Query opens over report | Al Camp | New Users | 6 | May 17th, 2004 04:35 AM |