excel to access conversion form problem

[ano]

dont know what is the best forum but my form is not the way i like
access because sql problem?
vba because sql and vba not same?
form because thats the result im working to!
excel-access.zip pdf explain my goal/problem
garcia.zip the obsolete excel
abc.zip the access imported excel
( pity i deleted all the 1st try so frustrating na that all keep failing and vba solved is)
but the sql is intriging
hope the pdf is understandable


and the result is confirming my doubt about my sql cq vbc

[June7]

Tables should be linked by primary key/foreign key fields. You have autonumber ID fields designated as primary key yet you do not link with those fields. Either change the primary key designation or use ID to link tables. The latter means saving ID values as foreign keys in related tables.

Why establish a relationship between Advance and Query1? Advance.dates does not have time parts but Query1.datum does.

There is no Payout form and no query referencing this form - in spite of information in Word document (not a PDF).

You want SQL to generate same dataset shown in tuptoa produced by VBA?

Why do some naam values in tuptoa have space? Why concatenate these data?

Define formula that derives these data in tuptoa table - what steps would I manually take to calculate data with pencil and paper?

naam	totaal	tegoed
20210916 vichappybeauty	$1,583.48	$1,183.48
20211016 vichappybeauty	$4,829.11	$4,329.11
20211116 vichappybeauty	$6,036.29	$2,036.29
20211216 vichappybeauty	$5,600.93	$1,100.93
20220116 vichappybeauty	$1,620.00	$620.00

several naam have multiple advance records for same date - how does this impact the vba calculation?


Why bother joining rates to exel when rates data comes from exel?


Why code to close tuptuo table - it should not even be open.


I approached SQL solution starting with 2 queries:

AdvSUM
SELECT advance.naam, CDate(Format([datum],"yyyy/mm/dd")) AS dte, Sum(advance.advanced) AS SumAdv
FROM advance
GROUP BY advance.naam, CDate(Format([datum],"yyyy/mm/dd"));

ExelABC
SELECT exel.id, abc.naam, exel.model, exel.dates, DateAdd("d",-15,[dates]) AS DteStart, exel.earn, exel.Rate, [earn]*[rate] AS Total, (SELECT TOP 1 SumAdv FROM AdvSUM WHERE AdvSUM.naam=abc.naam AND AdvSUM.dte BETWEEN DateAdd("d",-15,exel.[dates]) AND exel.dates ORDER BY dte DESC) AS Adv
FROM exel INNER JOIN abc ON exel.model = abc.abc
ORDER BY abc.naam, exel.dates;

I modified code to use these queries and that does create 23 records and all but 1 tegoed amount agrees with original code. I think it has to do with retaining time parts in date values. Why retain time parts in the VBA date comparison? You are using < and > for date comparison. What should happen if the values are equal?

Now I have to figure out why code returns only 23 records of the 81 in exel and apply that to query criteria. The code logic does not return records for cor, dal, fra, ind, gem, ghe, juv, lyn, mar even though they have data in advance. However, records for elo, mel, rem, sha, zaz are returned even though they do not have data in advance. Is this what you intended? sai is not in exel nor advance. I am stuck on implementing this logic in query.

[xps35]

hope the pdf is understandable

In any case, it is not clear to me at all. I suggest that you first explain in plain language what this is about and what the tables mean, for example.

[mike60smart]

Hi ano

Can you explain the following imported data:-

Date Period - September 16th To 30st 2021
Exclude Video Purchases
#	Performer	Billed	Earned
1	happybeauty	64.5	32.25
2	happywilling	20.8	10.4
3	happyretired	9	4.5
4	happydoggy	16.7	8.35
5	happylana	90.91	45.46
6	happyadios	7.8	3.9
Total Earned	$104.86

What are Performers?
Who is being Billed?
Who has Earned $104.86

[ano]

thanks for ur interest to help.
- primary auto number bec thats generated, even not needed
- the time is 8am so easy to add for compare/link
- payout is the result from earn - sum advance
- sql should be strong enough , but i did not manage so used vba
- space mean no earn ;; concatenated because lazy to add fields in database
- totaal is exchange*earn, tegoed is totaal - sum advance ;; check internet lookup the name copy the earn, check email to copy the exchange rate, multiply and finaly deduct sum loan/advance
- its a sum function just a loop if no sql
- rates dont belong in exel too many redundancy
- how can i fill tuptou if not open?
- advsum is 2 times a month 1st/16th 8am
- excelabc looks nice but i did not find between
- >< are ok because not allowed to use 8am
- simple explanation A) no earn but loan B) earn but no loan ;; A) means donate (lose) ;; sai start working but stopped without earn without loan

== the dates sheets are not complete bec add later to avoid typing numbers , so i found copy excel sheet value so import works ==

thanks for the effort i see i did miss some essentials
tc enjoy the new year

[ano]

performers are the workers
billed are the people who pay the sites 50% for site
earned is sum of workers send to the bankaccount from the coordinater the payout to be split out over the workers after taking percentage the bankholder get email with amount and exchange rate

[mike60smart]

Hi

What are these values ?

reden

fare

lazada

advance

pay

inday

goal

topay

Also can you explain the Formulae in the attached:-

[mike60smart]

Hi

Your table relationships would be along these lines:-

[ano]

the values are reminders why loan: lazada is like amazon, goal is no payout until reached

remove $ to get numeric value
multiply to get pesos
sum to check email with sheet
copy exchange from total

why need primary key if no manual input? index on the link fields should work and only those fields that are needed.
an extra primary key is only convenient if changes are expected, mostly needed by manual input

[mike60smart]

What does Reden mean ?

[ano]

reden is something like reason why

just amazed why so many questions abt meanings , i never understand what and why i just solve the problems. only access is not as straight forwards thinking after excel has so many more easy doings.
more strange to notice that the suggested import need soo many preparations before and after for a standard use
(remove double headings, improvise, copy value only, cut spreadsheet into max 255 columns before import and undo crosstab after import)
the analyze i try already but not recognize the bec it not recognizr the crosstab

[Salty Mariner]

just amazed why so many questions abt meanings , i never understand what and why i just solve the problems. only access is not as straight forwards thinking after excel has so many more easy doings.

Access and Excel are two entirely different products. Excel is primarily for analyzing data while Access is for storing and displaying displaying data. I would suggest that you decide what you want Access to do and then sit down with pen and paper and sketch out a model of how things are supposed to work. You are going to be fighting this forever if you keep trying to make Access behave like your spreadsheet. I have done this before and in some cases just decided I was better off sticking with a spreadsheet... mostly because things were too fluid to get locked into a strict model. Spreadsheet's can be quickly and easily changed and is one of the reasons they are so popular. It actually takes planning and a lot of time to develop a functioning DB Application. You will find yourself frustrated if you don't plan your database because the deeper you get into it the harder it will be to make changes.

[ano]

thnx
nice defining
as novice on access after being programmer for insurance and rent a hand , setup several systems like novel vms unix oracle.
solving problems from systems and sql.
i think i know diferences between excel and access. as easy oracle seems to be as hard access look like
choosing access is logical because its part of onedrive. and the spreadsheet is indeed handy but getting to slow.
i never suggested i wanted crosstab layout back in access, because thats easy but not as nice as the spreadsheet
all problems are related to import and forms. vba solve a lot.
trying to understand the use of the application needs too much time, solving is mostly quicker

[ano]

SELECT earn.md, earn.dt, earn.rn*rates.rt+(select nz(sum(advance.advanced),0) from advance where advance.naam=earn.md and advance.datum between earn.dt and rates_1.dt) AS pay
FROM earn, rates, rates AS rates_1 ' 2nd need for -1 rownumber
WHERE earn.dt = rates.dt and rates.nr -1=rates_1.nr ' own generated rownumber
ORDER BY earn.dt, earn.md;
the -15 was working but skip records because some loan were longer, na the between with previous record by own "rownumber"
works on abcvalues. add column and in vba update "rownumber" so its clean not miss numbers, redo if delete or insert

[Welshgasman]

i never understand what and why i just solve the problems.

To do that, you need to understand what and why ?, otherwise you are just hoping you get lucky.

If you understood the problems you are having here and in that other thread rae dates from Excel, then you would solve the problem.
Instead you just come here, then complain when people ask the what and why.