Creating a Form without a Table

[ronocoo]

Is it possible to create a form without a table? What about a form with unrelated tables?

Does it require me to know any coding, SQL? I'm not very technical with Access so hopefully I made myself as clear as possible.

Thanks

[nicknameoscar]

Maybe it would be better if you described what you are trying to do.

The answer to your question is yes, you can have forms not bound to a table or query. I can't remember if the form is said to be "unbound" or it is the controls in/on the form that are considered to be "unbound".

Anyway, I am not sure how the form would be useful if macros or VBA code or SQL wasn't used to either put data into the form controls or save data entered into the form controls. Maybe I am wrong.

[ronocoo]

Thanks Nick. Ah, yes. That's exactly what I'm trying to do. I want to create an unbound form that allows me to enter data. I'm trying to create a form for monthly cash projections.

Basically, I have five unrelated tables that contain different categories of transactions. The form should have the flexibility to allow me to enter the classes within the categories.

Is there a wizard that allows me to do this? I may have to create it using the blank form?

[June7]

You want one form to serve as data entry for 5 tables? These 5 tables are alike - same fields? If so, should be one table with another field for the category identifier. Then the form could be bound to the one table. Data entry is direct to table. Simple, no code.

Otherwise will need code to make the form functional for multiple like tables.

One option (my preference) is controls on form would be bound to fields that are named the same in all tables. Then code would set the RecordSource of the form when it opens to the appropriate table based on some criteria, such as a button click 'Edit Category A' for example.

Another is UPDATE or INSERT sql statements to send values to the appropriate table.

[ronocoo]

Hey June,

Yes, that sounds great. I really want to avoid coding since I don't know how. The 5 tables do have the same fields.

I have the data in one big master table and 5 separate tables. Which one should I be using? Individual tables or the master table? In the master table, the categories are combined with field name class.

[June7]

The master table. Should not have individual tables with duplicate data. What do you mean the categories are combined with field name class? This is one field with two pieces of data? Should be two fields. What is field name class?

[ronocoo]

I meant that in the master table, all the transactions are combined under one field named class. In the separate tables, they're different. The field name is the type of transaction. Let me give you an example of the excel file that I need to put into access.

	May 16, 2011	May 20, 2011	May 27, 2011	June 3, 2011	June 10, 2011
Cash Received	Current Week	One Week Out	Two Weeks Out	Three Weeks Out	Four Weeks Out
X	 $X 				
Y	 $X 				
Z	 $X 				
Total Weekly Cash Received	  $X  	 -   	 -   	 -   	 -   
					
Outstanding Checks	 $X 				
Cleared Checks	 $X

What's in common is the current week, one week out, etc.

The categories are Cash Received, Outstanding Checks, Cleared Checks, etc. My goal is to create a form that allows me to enter data so that I can input X, Y, Z under Cash Received and $X amounts. I also need it to total the categories if necesssary.

How should I go about starting? Using a blank form?

[June7]

You didn't mention having to import data from Excel in your initial post. Are you transferring this data into Access by manual data entry? Still not quite understanding the structure of 'master' table and the 5 individual tables. Do you now have data in these tables? Is data in the master same as in the individual tables, just arranged differently? You need to choose which arrangement to use - the current master table or a combined table of the 5 individuals.

This doesn't look like raw transactions data. It looks like summations. What do X, Y, Z represent? You need to input the OutstandingChecks and ClearedChecks values?

[ronocoo]

You didn't mention having to import data from Excel in your initial post. Are you transferring this data into Access by manual data entry? Still not quite understanding the structure of 'master' table and the 5 individual tables. Do you now have data in these tables? Is data in the master same as in the individual tables, just arranged differently? You need to choose which arrangement to use - the current master table or a combined table of the 5 individuals.

This doesn't look like raw transactions data. It looks like summations. What do X, Y, Z represent? You need to input the OutstandingChecks and ClearedChecks values?

Hey June,

I won't be importing data from Excel. I just want to create a form similar to the Excel with the same headers and categories that I can input into and have it appear the same way. I do want to total the columns though since it needs to total the Cash Received that week.

For example: When I enter Client X under Cash Received, it should input the Client Name under Cash Received and the same for the dollar amount under Week designated.

The categories is the first column (Cash Received, Outstanding Checks, Cleared Checks, etc.) then the rest to the right are the same for all categories.

[June7]

Whoa! Access is a relational database, not a spreadsheet, can't think in spreadsheet terms. Designing tables like that spreadsheet example is not properly normalized data. Tables are limited to 256 columns. Do you plan to repeat the fields for each date period? How long before you run out? You need to consider that maybe Access is not the correct vehicle for your situation.

[ronocoo]

Thanks June, I'll look into other vehicles to use.

What about if I made several unbound forms, could I combine it into a single report?

There would be only one field that is different which is the type of transaction, the other fields one week out, two weeks out, etc are similar.

[June7]

Reports and forms can have sub reports/forms.

So the master table has the same fields as the 5 individual tables, with the addition of the Category field as the only variance. Still doesn't address the fact that this is not normalized data. My question stands on whether the 'week out' fields need to be repeated for each date period (every month and every year)? Or is this 'week out' pattern carried out to infinity?