Where to start?

[AccessNewby]

This is my first project using Access so take it easy on me. I'm trying to convert the Excel application to Access since I think Access is more flexible/powerful. I'm not sure if this requires programming (VBA-over my head) or just need some complex formulas to get the mission accomplished.

The objective is to be able to copy and paste data from a customized form directly to Access. These data will automatically be added into a summary sheet based on volume and amount.

Thank you in advance.

AccessNewby

 

[mhartman]

Hello and welcome:

Thats great, but what can we help you with in this project.

Regards
Mark

 

[AccessNewby]

Please review attachment.

Tab 1 (Form A) is a System data form and Tab 2 (Form B) is a Office data form (there are 7 different offices). The objective is to be able to copy and paste these forms to a template, where the data will automatically be added into a summary sheet based on volume and amount in Tab 3 (Summary sheet).

Thank you Mark for sharing your brain cells.


AccessNewby

 

[mhartman]

Hello:

From just a quick look at your spreadsheet, it looks like you want to have a relational database design with a parent table called offices and a child table called Data for instance. I put together a basic design from what I could understand of your spreadsheet.

Regards
Mark

 

[AccessNewby]

Thank you so much for taking your time to reply.

I don't think I explained my objective clearly in my previous post so let me try again. I just created my first table called Historical data (see attached). Each weekday, I get numerous emails from different offices reporting their volume and amount. Is there a way that I can set up a format form where I can copy and paste this data and Access knows where to place these volume and amount into the correct field of the Historical data table?

TIA,

AccessNewby

 

[Dreamweaver]

Q: How are you receiving this data IE
1, Excel
2, Text File
Etc
Is this info formated ALL the same from ALL Departments.
If not can it be via a template you supply

Or why not if you can use access so each department has a front end to add there info.

Importing into most systems ain't a problem as there are a number of ways to get a result you just need to set not just the system but the people sending data up in a way that makes life a little simpler.

I send out and import through import/Cleaning tools between 6K AND 50k A week to users all over the planet.

http://www.databasedreams.co.uk/chart_tracker_Free/satellite_importer_gallery.html

I use temp tables with screens to copie past details from the WWW which are the correct format for the site importing from then it's just a matter of attaching the new details to your db but you may have to add the details in the correct order allowing for your relationships if any.

hope it helps in some small way.

mick

 

[AccessNewby]

Thank you Mick for taking time out to assist me.

The data is sent in a text file in a table and all the offices use the same format. Please see attachment. The only data that I need to extract from this text file is the volume and amount (A & B) and the date. How can this data be imported over to the Historical data table? and If I have another office would it know to add this data also?

TIA,
AccessNewby

 

[Dreamweaver]

Thank you Mick for taking time out to assist me.

The data is sent in a text file in a table and all the offices use the same format. Please see attachment. The only data that I need to extract from this text file is the volume and amount (A & B) and the date. How can this data be imported over to the Historical data table? and If I have another office would it know to add this data also?

TIA,
AccessNewby

If I have another office would it know to add this data also?

Only if you tell them?

are all these offices in the same building or linked to the same server???

if so I would put a small db together and give each a front end and put the data file on a server so they add the data directly all you may wanna do is tag all new enties so you can validate the entries.

but if not I would get them to use excel as text files need a delimiter to import and chances are they wont have a clue what that is and trying to explain that they need to use one "Ouch" Excel is simple as you can import using a specification for the cells you need.

Mick

 

[AccessNewby]

Unfortunately these offices are not in the same building nor on the same server. Can I create a form template that links to the Historical data so when I copy and paste the information from these offices to the template it would transfer over to the Historical table?

What is a "delimiter" (from your post below)?

Thank you.

AccessNewby

 

[Dreamweaver]

If you post a blank copy of your db I'll see if I can sort you something that will do the job.

I would send then a blank excel sheet when you need them to supply the New numbers they then send it back completed.

 

[AccessNewby]

I attached a blank DB for you but don't you need to keep the Historical table in there for the Summary info? I'll trust you since I don't know what I'm doing.

Thank you for your time

AccessNewby:

 

[Dreamweaver]

You may be right there but for now I'll see if I can work a way where the data your sent can be added simply.

I'll work with an excel sheet as it's simpler But regardless of if it's excel or text the data you get from the offices will need to be formated how the program needs it or your back to square one with editing it before adding.

I'll download it tomorrow and have a pop in a day or two as in the middle of a Production Management system and my deadlines not that far away so looks like I'll be working over Xmas as usual

Speak soon

Mick

 

[AccessNewby]

Mich..Please do not work over Christmas holiday on my account. Enjoy your Christmas with your friends and families My project is not that urgent. I'm trying to study Access and VBA online as much as possible so don't inconvenience anyone. Anytime you need R&R and in Atlanta..you can stay with my family.

MERRY CHRISTMAS! Thank you for your time and brain power.

AccessNewby

 

[Dreamweaver]

Don't worry yourself I do things like this as I enjoy it my clients are a different ball game altogether they sometimes want The Mission Imposable but clients are always right LOL (When they pay enough LOL) so Needs must I'll look at yours and post it back soon as I have something working.

Marry Xmas

Mick