separate a form by months

[graciemora]

I have a form, where i need to keep records by months, but i copy that form to a new form with different month name It copies everything.
is there a way to have a new form with the same fields coming from the same source or do I need just to filter it? Thanks for any help provided.

[jzwp11]

You don't keep records in a form. The form just displays the records that are in the table to which the form is bound. You can do this with 1 form and just filter the table's data that is displayed in the form. In order to help you further, we need to understand your table structure. Can you provide that? You can either post a copy of your database (with any sensitive data removed) or you can provide the structure similar to the following format (table name followed by field names):


tblPeople (table)
-pkPeopleID primary key field, autonumber (datatype)
-txtFName firstname field, text (datatype)
-txtLName lastname field, text
-txtSSN social security number field, text
-txtAddress address field, text
-txtCity city field, text
etc.

[graciemora]

hi, Im sending you a copy of my DB please give me suggestions. The table "over/short transaction table will be populate with the input form with the same name. please help me!!!

[jzwp11]

Unfortunately, I do not have Access 2007 here at work (a little behind the times as usual). I can take a look at it from home this evening. Alternatively, if you are able to save the database in an earlier version of Access which you can do via Access 2007 and post that, then I would be able to look at it sooner.

[graciemora]

I can not save in an earlier version because uses features that requiere the current file format. (that what it saids!) I appreciate your time. thank for your help.

[jzwp11]

I was going to try to save it in an earlier version as well and bring it to work this morning, but I got the same message. I did not see anything in your tables that uses some of the new 2007 features, so I am not sure why it would not save, but that is not relevant to your initial question.

I did take a quick look at your database last night, but was unable to spend as much time as I wanted to. I did notice that you have a field in your transaction table called form#, so that led to some confusion because your initial question referred to forms so I did not know if you were referring to a specific Access form or a form# relative to your data. My initial reaction is that you have to filter the data going into the Access form. Do you want to only see the records pertinent to the current month in your Access form? If so, then you would set up a query and base the Access form on that query. You will need some date field in order to do that filtering. You will need to use the month() and year() functions in conjuction with the date() function (which returns the current date). The query might go something like this:

SELECT...
FROM yourtable
WHERE month(yourdatefield)=month(date()) and year(yourdatefield)=year(date())

As a general observation, it is recommended to not use spaces or special characters (#,&,* etc.) in your table or field names. Using them forces you to enclose all table and field names in square brackets which get to be a hassle over time. Additionally, some of the special characters are used in Visual Basic for Application (VBA) code and could lead to problems.

[graciemora]

HI thanks for your time. The form # field that you saw in my transaction table is the base of my form. transaction table=transactionquery= transaction form. I already change the Form# field name (FormNumber)
FormNumber field is to keep records as unique.
in my transaction query I use () to bring together first name, and last name in the same field, when I tried to use the other tables to create a query with out the transaction table give me a blank query. and I am not able to input data, the real tables contain 4000 records. I never taught it was going to be to hard to create a simple input form. I have a date field in my form. can I filter from that field?

[jzwp11]

Now that you have provided a little more detail, I will have to look at your database some more this evening. I understand that you are recording transactions. Could you tell me in a little more detail of what your application is designed to do that will give me a foundation when I look at your database tonight.

[graciemora]

I want to use the form "over/short form" as input form, everyday we need to input data in the form. (data entry) on this form we need to keep records of beer that drivers did not bring back, as you can see on "reason codes tables" those are the reason that a driver can use. but something they do not have an explanation and we will charge them for that, I would like to keep records by week, and by month to create reports, to know who is the driver with more cases of beer short, and qty and total amount. thanks for your help I appreciate it.

[jzwp11]

So you actually record the data everyday. You can use the date entered to get the weekly, monthly or yearly reports. I'll take a closer look at your database later tonight

[graciemora]

Yes, I am adding records to my form every day. before I used to do it in a piece of paper and I had a BIG folder, with a lots of copies, thanks..

[jzwp11]

I took a good look at your database, and unfortunately I see many problems. I will just list each item I saw with a short explanation of why it might cause problems

1. It is recommended to not have spaces or special characters (#, &, /,\ etc.) in your table or field names. Having them forces you to put square brackets ([]) around them which takes more time when you are constructing queries and writing code. Additionally, some of the special characters are used in code and having them in field and table names can cause the code to fail

2. Having look ups at the table level is not recommended; see this link for more details

3. Repeating the same information in more than one table. The purpose of a relational database is to avoid repeating information in more than one place.

4. Separate tables for employees that are drivers or data entry people. All employees should be in 1 table (like data in 1 table).

Now as to your table structure itself. I was confused about the tables related to the departments and the jobs. The best I could tell, one department could have many jobs associated with it; this describes a one-to-many relationship.

An employee is associated with 1 job, so you only need to link the employee to the jobID. You do not need to repeat the department information in the employee table nor do you need to repeat the text of the job since you link by the jobID field.

You have customers that can place orders, but you did not have a place to put the details (beers) of the order.

I was quite confused by your transaction table. I assume that on a given day, a truck is loaded up with various orders for delivery to the customer, correct? I assume that a load can consist of many orders (one delivery load to many orders relationship). You have a driver assigned to make the delivery. Is there only 1 employee that makes a particular delivery or can multiple employees take part in the delivery (driver & a helper perhaps)?
Your drivers make the deliveries, but in some cases, the customer does not get what they ordered. They might be short on quantity and you want to capture that.

Now, the question is, do you want to capture all of the details of the deliveries or only those items that were shorted?

In your beers table (which I have renamed to tblItems), there are several price fields. If an item can have many prices then that describes a one-to-many relationship which would require 2 tables. I wanted to understand what these various price fields were for before making any additional changes.


Based on the issues I found and my best guess of what you are trying to do, I corrected your database as best as possible. I also converted the database to an earlier version of Access; it is attached.

As to your original question, that will have to wait until we fix the remaining issues with your table structure.

[graciemora]

thanks you are the first person that have take time for my database, I will work on my DB this weekend. Are you a teacher you are really fast doing all those changes!! thanks very much. I let you know on Monday.

[jzwp11]

You're welcome. No, I'm not a teacher; I've been working with Access for quite a while now, mostly as a hobby. I've done some Access stuff at work to help organize things, but it was not a formal part of my job.

[graciemora]

I removed all characters, look ups, drivers table, data entry tables, check my tables if I removed the repeatative info.! I do not need to keep record about customer orders. Loadnumber=driver's truck number, An invoice contain cust order information. each driver has a LOAD, with differentes customersaccounts, customeraccounts has theirs itemsordered, when a driver comeback sometimes he is missing beer cases, with no reason (Reasoncodesfield), so i created the transaction table to keep record of that, to see if driver need to be charged, but I need the dataenty person name(Revised by)field to see who is charging the driver in the office, same thing with warehouse person. but I use the lookups to keep the info integrate that way I won't have problems in the future with filters .Each item has differentes prices, (regular price; and sale price) cashand carry are special accounts samething(regularprice; and saleprice). Based on tbltransaction I would like to have a Nice Input fom to make it easy to put data every day, Like you I am a simple employee in this company but I want to do ti easy, and I am taking Access classes. thanks for your help and I hope you don't get tire of my questions.