need help formatting for database

[homer15]

hey everyone,

attached is a spreadsheet that has two tabs:
what i'm able to get, and what i need to have.

any help on automating the process to get the data in the format i need is very very much appreciated. this is going to be going into a simple access database, but the report is going to be initially built in excel.
thanks a lot!

[homer15]

any help at all is appreciated.

[Gizmo]

Well, the best thing I can come up with is to create a macro. But other than that, I don't really know.

I'm a newbie still

[homer15]

yea, i know i'll need a macro, but i don't know how to make the macro do what i want.

[Gizmo]

Hehe, now that you've said that, my knowledge comes from the time my mum was taking Spreadsheet computer courses. Buut nayway, find the "Macros..." option in one of the file menu titles, drag your mouse over it and another list should slide out, showing all the different options for the macro, you can record the actions you have to do on the macro and then assign to either a keyboard key or an icon on your taskbar.

[homer15]

thanks dbg. i'm pretty familiar with recording macros, actually, most of the time i write the code myself and i can get them to do some pretty complex things, but this one is giving me problems. i just can't figure our how to consolidate the freight bill, bill of ladings, and drivers for each invoice into one record (row).

[sdkfz]

Homer15,

I presume

Invoice is the primary key - no two invoices can have the same number
Date - duh
PO - Purchase order Number
FB - inventory/item number?
BOL - ?
Driver - employee ID

Table One - think of it as a basic info- it only has pertinant info for the invoice that does not need repeating elsewhere (number, date of invoice etc)

Invoice Number - text field -pick a field length that will cover expected usage, make it a primary key

Date- date/time field with short date unless you need times too in which case make it a long date

Anything else that should be here like who made delivery/ who acepted delivery etc.

Since the invoice numnber is unique in the first table the next table will allow you to have multiple items in the invoice.

Here you have invoice number, inventory number and any other fields that may be needed like count of items ordered.

The two table are linked together by invoice number and the link is a 'one to many' (relationships toolbox) so that the one unique invocie number in the first table will pull in all of the items ordered.

Now how to import. I'd set up the tables in access first, split the excel data into the parts needed by columns for the tables you decide upon and do not worry about the duplicate invocie numbers yet. Make a copy of the first table and delete the primary key aspect of it. Import the invoice number data in, then append the imported with duplcate invoice numbers over to the final table. It will tell you during the running of the query that it could not append all records which is exactly what you want because you are deleting the dups. You can delete the temp invoice table (with dups) and then import the other half of the data to the second table.

Now a form can have only one table refernced, but you can have subforms in a form linked to other tables and of course a query can have several table linked together but I am probably getting ahead of the game at this point.



Hope that helps

[homer15]

do you konw how this would show up if i were to export it out of access?

[homer15]

ok, i was able to do that just fine, now i have to figure out how to get all of the data to appear in one index (like all of the freight bill numbers for an invoice would be concononated into one index field). i'm sure there's a way to get it to query like that, but i'd appreciate any help on the matter.

[sdkfz]

You need to use queries and reports to pull this together the way you want.

In a report you can use sorting and grouping to pull together the pieces you want to go together. When you use this feature you can give each 'group' a header and a footer for titles, totals/averages etc and still have a report footer for total of report, group by invoice number and then sort by what ever you need if at all.

Of course a report pulls from a query typically so create a select query to get the data together. On the query you can use a parameter on the pertinent field to only get what you want. In the criteria line type in:

Between [my cute message for the first record] and [my cute message for the last record]. So an example for a date based report would be on the date field criteria "Between [Enter start date:] and [Enter end date:]"

When you run the query a pop up with your inside the brackets will appear, you enter the date click on OK and then another pop up comes up with the second message. This way you do not have to design a "annual report" a "quarterly report' etc the dates entered are up to the user. You can also make this fields appear in the report itself so there is no confusion after the report is run if this was an annual or quarterly report. Of course this concept would work for invoice numbers, driver ID's etc.

As to exporting out of access you can export reports into word and excel in the format (visually that is) they are in (word looks a bit better if you ask me)